Need Help with Excel If,and,then Formula?

Question

I hope you can understand what I am trying to do here. I have a chart, and a value that I came up with a different formula. I know want to take that value in B16 and compare it to the chart and depending where it falls, then go over and do the math for the line that corresonds to the value of B16. This formula below is as close as I can get, but it does not work. Any help would be greatly appreciated.

=If(B16>=(F18)and<=(G18),round((round((B16-J18)*I18,2)+H18)/26,2),IF(B16>=(F19)and<=(G19),round((round((B16-J19)*I19,2)+H19)/26,2),IF(B16>=(F20)and<=(G20),round((round((B16-J20)*I20,2)+H20)/26,2),IF(B16>=(F21)and<=(G21),round((round((B16-J21)*I21,2)+H21)/26,2),IF(B16>=(F22)and<=(G22),round((round((B16-J22)*I22,2)+H22)/26,2),IF(B16>=(F23)and<=(G23),round((round((B16-J23)*I23,2)+H23)/26,2),IF(B16>=(F24),round((round((B16-J24)*I24,2)+H24)/26,2))

"=If(B16>=(F18)and<=(G18),round((round((B16-J18)*I18,2)+H18)/26,2),IF(B16>=(F19)and<=(G19),round((round((B16-J19)*I19,2)+H19)/26,2),IF(B16>=(F20)and<=(G20),round((round((B16-J20)*I20,2)+H20)/26,2),IF(B16>=(F21)and<=(G21),round((round((B16-J21)*I21,2)+H21)/26,2),IF(B16>=(F22)and<=(G22),round((round((B16-J22)*I22,2)+H22)/26,2),IF(B16>=(F23)and<=(G23),round((round((B16-J23)*I23,2)+H23)/26,2),IF(B16>=(F24),round((round((B16-J24)*I24,2)+H24)/26,2))"

So, I think I am getting closer, but it still does not work. =If(and(B16>=(F18),B16<=(G18))),round((round((B16-J18)*I18,2)+H18)/26,2),IF(and(B16>=(F19),B16<=(G19))),round((round((B16-J19)*I19,2)+H19)/26,2),IF(and(B16>=(F20),B16<=(G20))),round((round((B16-J20)*I20,2)+H20)/26,2),IF(and(B16>=(F21),B16<=(G21))),round((round((B16-J21)*I21,2)+H21)/26,2),IF(and(B16>=(F22),B16<=(G22))),round((round((B16-J22)*I22,2)+H22)/26,2),IF(and(B16>=(F23),B16<=(G23))),round((round((B16-J23)*I23,2)+H23)/26,2),IF(B16>=(F24),round((round((B16-J24)*I24,2)+H24)/26,2))

A couple of you were right. I needed to change where I had my AND and I had to take out some of the parenthesis.

=If(and(B16>=F18,B16<=G18),round((((B16-J18)*I18)+H18)/26,2),IF(and
(B16>=F19,B16<=G19),round((((B16-J19)*I19)+H19)/26,2),IF(and
(B16>=F20,B16<=G20),round((((B16-J20)*I20)+H20)/26,2),IF(and
(B16>=F21,B16<=G21),round((((B16-J21)*I21)+H21)/26,2),IF(and
(B16>=F22,B16<=G22),round((((B16-J22)*I22)+H22)/26,2),IF(and
(B16>=F23,B16<=G23),round((((B16-J23)*I23)+H23)/26,2),IF(B16>=F24,round
((((B16-J24)*I24)+H24)/26,2),0

Answer 1

use:



=if(AND(B16>=(F18), B16<=(G18)), round...,...)


You can have more than two 'items' in the AND statement, seperated by a comma.

(Tip: you can also use OR......can get some quite complex rules with a combination)


...TYPE OUT THE FULL FORMULA YOU ARE USING....THIS METHOD SHOULD WORK.....

Try using the formula:

=IF(AND(B16>=(F18),B16<=(G18)),1,0)

If you get an error then you have a problem in cell B16, F18 or G18. If you get either a 0 or a 1 then the formula is working - therefore the problem would be in the 'round.....' formulad that you added...


BTW I agree with the answer below...



If you are trying to look up the answer in a table, you're better using VLookup....


Email me if you want an example of the use of VLOOKUP...

Or go to this site for a decent explanation of the use....

http://www.mrexcel.com/tip096.shtml

Answer 2

Could you post your current formula with line breaks -- the whole thing is not displaying. Check in Preview before you post it.

One minor point: you never need to put parentheses around just a cell reference, like F18

Answer 3

Hi,
Why don't you use VBA (press alt + F11) and then write a "sub" when the value of B16 (Cells(2,16).value) changes it does all your calculations for you, I am sure it will work better.

Answer 4

Somewhat difficult to understand, but if you are using a lookup chart, go to Excel help and search for VLOOKUP and HLOOKUP. You'll probably have to do a VLOOKUP.

Answer 5

Do you have MSN? If you do I can help you solve your problems, however being able to actively talk to you would be a huge help.