Need help with IF nested formulas

S

sadata

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?
 
P

PCLIVE

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
 
T

T. Valko

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
 
R

Rick Rothstein \(MVP - VB\)

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
 
P

PCLIVE

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



--
 
Top