What's wrong with this formula?

zakasnak

Active Member
Joined
Sep 21, 2005
Messages
307
=IF(H733<100,"<100",IF(H733<300>100,">100<300",IF(H733<500>300,">300<500",IF(H733<1000>500,">500<1000",IF(H733<7500>1000,">1000<7500",IF(H733>7500,">7500",""))))))

It stops after<300>100.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

kkknie

Well-known Member
Joined
Apr 29, 2002
Messages
677
First of all, I'm not sure how H773<300>100 would even be interpreted. In general, you would need to use AND(H773<300,H773>100).

Even better, since this is a nested if, you can eliminate the greater than check (since if H773 was <100, it would never reach the second IF). You can also eliminate the last if. Can probably rewrite to:

=IF(H733<100,"<100",IF(H733<300,">100<300",IF(H733<500,">300<500",IF(H733<1000,">500<1000",IF(H733<7500,">1000<7500",">7500")))))

Also, you might want to think about using <= rather than <, since nothing will be returned if the value is 100, 300, 500 or 1000.

Hope this helps,

K
 

dcardno

Well-known Member
Joined
Nov 20, 2002
Messages
543
Office Version
  1. 2013
Platform
  1. Windows
A couple of things:
1) You can't test for "between" in that way: you need to test for the two conditions of ">100" and "<300" and also test that both conditions hold, either using an "and" function or multiplying the truth values. The formats are:
Code:
=if(and(H733>100),(H733<300)),"Between 100 & 300",next test...)
or
Code:
=if((H733>100)*(H733<300),"Between 100 & 300",next test...)

2) Once the formula starts evaluating past the first branch you know the value in H733 is greater than 100, so you don't need to evaluate it again. You can present this sort of logic as:
Code:
=if(H733 < 100,"Small Value",if(H733 < 300, "Between 100 & 300",If(H773 < 500,"Between 300 & 500",If(H733 < 7500, etc ....  ))))

3) Oftentimes, it is easier to write and (particularly) maintain such logic as a Lookup Table
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
=LOOKUP(H733,{0,100,300,500,1000,7500;"<100",">100<300",">300<500",">500<1000",">1000<7500",">7500"})

I'm assuming you only have positive values in H733......
 

Forum statistics

Threads
1,136,600
Messages
5,676,727
Members
419,647
Latest member
usas12gthr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top