Mulitple OR's using IF's?

[jerd0708]

I have a problem that is driving me crazy.

I have data coming to a cell, it will be between 0.00% and 0.50%

I want to have a weighted percentage using this data.

So if Cell E39 is 0.00% I want E40 to read 0% but if it E39 is 0.01% to 0.10% I want it to read (-10%) 0.011% to 0.20% reads (-20%) and so on up to 0.50 and reading (-50%).

I have tryed all kinds of AND's OR's and IF's and nothing is working. I have a very love hate relationship with Excel!

Thanks to anyone that can help.

 

[repairman615]

Give this a try:

=LOOKUP(E39,{0,0.01,0.11,0.21,0.31,0.41,0.51},{0,-0.1,-0.2,-0.3,-0.4,-0.5,0})

Test it fully to be sure.

 

[jproffer]

=IF(AND(D4>0,D4<=0.1),10,IF(AND(D4>0.1,D4<=0.2),20,IF(AND(D4>0.2,D4<=0.3),30,IF(AND(D4>0.3,D4<=0.4),40,IF(AND(D4>0.4,D4<=0.5),50,"HIGH VALUE")))))

That is another way.

OOPS, missed the negatives...you can adjust the whole numbers in the formula to suit yourself...those are the "if true" values.

 

[repairman615]

Unite!




=IF(AND(E3>0,E3<=0.1),-0.1,IF(AND(E3>0.1,E3<=0.2),-0.2,IF(AND(E3>0.2,E3<=0.3),-0.3,IF(AND(E3>0.3,E3<=0.4),-0.4,IF(AND(E3>0.4,E3<=0.5),-0.5,"HIGH VALUE")))))

 

[jerd0708]

In both of them I am just getting a "False"? I am working on it but I can not come up with anything?

 

[repairman615]

Did you make sure the cell referenced is E39 in the formulas?

 

[jerd0708]

Yes in every spot. I will try again though.

 

[Jonmo1]

The lookup is the way to go, did you try it?

 

[jerd0708]

The LOOKUP worked I had to change the max value (0) from the results to make it -0.50 so that anything above the highest value was the max. Also had to play with what was coming into E39 a little because it was coming in percentage and the formula did not like it.
I can not thank you guys enough for the help.