Need help with IF nested formulas

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a list of parameters I want 1 cell to calculate: e.g.
=IF(A1<0.3),1,IF(A1>0.3,A1<1.6),2,IF(A1>1.6,A1<3.1),3,....ETC. I KEEP
GETTING AN ERROR MESSAGE...IS THERE SOMETHING I AM MISSING?
 
You need to use some ANDs.

=IF(A1<0.3,1,IF(AND(A1>0.3,A1<1.6),2,IF(AND(A1>1.6,A1<3.1),3,"etc.")))

As long as you're not nesting more than 7 IFs, then this should work.

HTH,
Paul
 
How many "ETC's" are there?

One thing you're doing wrong in your formula is you're excluding the numbers
between intervals.

For example, you're testing for <0.3 and >0.3 but you're not testing for
=0.3.

If you have many "ETC's" then you'd be better off building a 2 column table
and using a lookup formula. Like this:

...........A..........B
1........0...........1
2........0.3........2
3........1.6........3
4........3.1........4

Then, use a formula like this:

A10 = 1.5999

=IF(A10="","",VLOOKUP(A10,A1:B4,2))

Result = 2
 
You need to use some ANDs.
=IF(A1<0.3,1,IF(AND(A1>0.3,A1<1.6),2,IF(AND(A1>1.6,A1<3.1),3,"etc.")))

As long as you're not nesting more than 7 IFs, then this should work.
Click to expand...

Actually, assuming the OP did not mean to deliberately exclude the break
points at 0.3, 1.6, 3.1, etc., the need for AND can be eliminated (because
of the overlapping excluding ranges)....

=IF(A1<0.3,1,IF(A1<1.6,2,IF(A1<3.1,3,etc.)))

Rick
 
You are correct. As long as there aren't negative numbers, which I don't
suspect there to be, then you formula is correct. Else I suspect there'd
have to be one more IF condition at the beginning.

In any case, thanks for pointing out my overkilling of the formula.

Regards,
Paul



--
 
Back
Top