Error with IF Formulat

[Nsharp]

hello this is my first post on this board. I am hoping that someone can tell me what is wrong with my formula

<TABLE style="WIDTH: 344pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=457><COLGROUP><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2121" width=58><COL style="WIDTH: 211pt; mso-width-source: userset; mso-width-alt: 10276" width=281><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4315" width=118><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 44pt; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20 width=58><</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 211pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 width=281>7499</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 89pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 width=118>NO Rebate</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64>7500-14999</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>0.245</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64>15000-25000</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>0.23</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>25,001</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>0.215</TD></TR></TBODY></TABLE>


Here is the formula I created the first two show a valid result but from 15000 to 25001 I get the 7500 answer.....

=IF(A1<=7499,"No Rebate",IF(A1>=7500,"0.245",IF(A1>=15000,"0.23",IF(A1>=25001,"0.215"))))

 

[Jonmo1]

Welcome to the board..

Excel reads the IF conditions one at a time, from left to right.

The first (and only the first) one to result in TRUE is executed, the rest ignored.

So with your formula, if A1 is say 18000
It reads
Is A1 <=7499 - False
Is A1 >=7500 - TRUE - Returns 0.245
Ignores the rest of the Ifs.


To resolve this, put the Higher criteria first

=IF(A1<=7499,"No Rebate",IF(A1>=25001,"0.215",IF(A1>=15000,"0.23",If(A1>=7500,"0.245"))))


Hope that helps.

 

[barry houdini]

Hello Nsharp, welcome to MrExcel,

Try starting from the highest value and working down, i.e.

=IF(A1="","",IF(A1>=25001,0.215,IF(A1>=15000,0.23,IF(A1>=7500,0.245,"No rebate"))))

or a LOOKUP formula

=IF(A1="","",LOOKUP(A1,{0,7500,15000,25001},{"No rebate",0.245,0.23,0.215})

 

[Jonmo1]

Now, the best solution is to not use If at all..
Instead, use Lookup..

=LOOKUP(A1,{0,7500,15000,25001},{"No Rebate","0.245","0.23","0.215"})

This is much more flexible and easier to adapt/change as needed.

Keep the first array {0,7500,15000,25001} sorted ascending smallest to largest / left to right.

 

[Nsharp]

Re: Error with IF Formula

wow thank you so much for the extremely fast reply! That makes sense I appreciate your help! Have a wonderful day!

 

[jasonb75]

Welcome to the board!

If you're going to use that method you need to have your values in descending order, the formula is never reaching the false statement because 15000 is still >=7500.

=IF(A1>=25001,0.215,IF(A1>=15000,0.23,IF(A1>=7500,0.245,"No Rebate")))