Forum Home
Press F1
 
Thread ID: 92966 2008-08-29 19:19:00 Need Help on Excel Formula Nironto (14134) Press F1
Post ID Timestamp Content User
701306 2008-08-29 19:19:00 H guys,
would appreciate if anyone can help me out.

I have 2 fields( A1 and A2) to compute a 3rd field B1. The field of "A1" can either be HOT or COLD. Same for A2.

So there is 4 possible permutations.

A1 A2 B1
HOT COLD = 1
HOT HOT = 2
COLD COLD = 3
COLD HOT =4

So what i need is a excel calculation formula that if e.g. A1 ( value = Hot) and A2 (value = Cold) , then B1 = 1... etc.

Any help would be appreciated! 		Nironto (14134)
701307 2008-08-30 06:24:00 This is a rough and ready solution. It assumes that you are entering only HOT and COLD items, upper case, because it works on the initial letters. Calculates on ASCII value of H and C. H= 72 and C=67. If you enter any different text then you will get wrong answers.
Enter this formula in cell B1
=IF(CODE(A1)+CODE(A2)=144,"1",IF(CODE(A1)>CODE(A2),"2", IF(CODE(A1)<CODE(A2),"3", IF(CODE(A1)=CODE(A2),"4","X"))))
If there was a risk of other text being entered then you would need more =IF to report “error” for false data. 		coldot (6847)
701308 2008-08-30 06:37:00 Hi Coldot,
What can I say? You rock!
It works like magic.

Regards,
Nironto 		Nironto (14134)
701309 2008-09-01 08:34:00 Hi guys,
A few questions which cracking my head for a nerd like me .

PART 1
What happens if it is "0" value for either A1 and A2?
In such case, then I would like to display a value "0" for B1 if any of A1 or A2 are "0" value .

e . g
A1 A2 B1
HOT 0 = 0


A1 A2 B1
HOT COLD = 1
HOT HOT = 2
COLD COLD = 3
COLD HOT =4



PART 2
2nd Question which I have .

For Cell F1, it is pulling data from a cell E1 . (numeric value)

F1 =IF(NOT(E1<=49), "HIGH", "LOW")

So If E1 = 50 and above( High), E1 = 49 and below (Low, including) .


What if I need a third value where E1 = "0", ( value =0), E1 = LOW (value = 1-49), E1 = HIGH (value = 50-100)



PART 3
My current formula is AutoSum ( C30 - C50) .
This is not what i want .

Part A = C30 to C40
Part B = C41 to C50
Either Part A or Part B should be used instead (and not the sum of Part A + Part B) .
If Part A is selected, Part B is void . vice versa .

What i need is . . .
Subtotal = Either (Sum of C30 to C40) or (Sum of C41 to C50)
How do I go about it?


I do not know how to thank you guys enough . 		Nironto (14134)
701310 2008-09-02 11:59:00 Hi

Part 1:
The following formula assumes the HOT and COLD values are in cells A1 and B1, this formula is entered into C1:
=(1-(OR(A1=0,B1=0)))*(IF(LEFT(A1,3)="HOT",2,4)-IF(LEFT(B1,3)="HOT",0,1))

Change the cell references to suit.

Part 2:
=IF(E1=0,0,IF(E1<=49, "LOW", "HIGH"))

Part 3:
Where / how do you specify which part you want to do the autosum for? There are many ways of doing this but it depends on how your data is laid out and where/how you capture which part you want to sum.

Andrew 		andrew93 (249)
701311 2008-09-03 14:32:00 Thanks Andrew!

With help and guidance from so many gurus in this forum, I have grown and learnt a lot. A good knowledge sharing experience!

Managed to come up part 3 after trying for some time and works..

Here's the solution for friends with similar problems in future.

On Cell M10
=IF((A1=0),SUM((B1:B5)),IF((A1>0),SUM((B6:B10))))

On Cell N10 (enter here to display the error message)
=IF(AND(A1>0,C1>0)=TRUE,"ERROR! Please Enter either A or B",0)

Once again! Thank you everyone for the guidance!

Regards,
Nironto 		Nironto (14134)
701312 2008-09-04 09:04:00 Hi

Be careful with this one:
=IF((A1=0),SUM((B1:B5)),IF((A1>0),SUM((B6:B10))))

Enter a negative value into A1 and see what the formula returns.

A 		andrew93 (249)
701313 2008-09-04 16:22:00 Special thanks to Andrew and Coldot for highlighting potential problem. Think I will overcome this by inserting option boxes so that the values are fixed and pre-determined. ^_^

Regards,
Nironto 		Nironto (14134)
1