Need help with IF nested formulas
 

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

Need help with IF nested formulas
 

You need to use some ANDs.

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

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

HTH,
Paul

--

"sadata" wrote in message
...
I have a list of parameters I want 1 cell to calculate: e.g.
=IF(A1<0.3),1,IF(A10.3,A1<1.6),2,IF(A11.6,A1<3.1 ),3,....ETC. I KEEP
GETTING AN ERROR MESSAGE...IS THERE SOMETHING I AM MISSING?

Need help with IF nested formulas
 

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

--
Biff
Microsoft Excel MVP


"sadata" wrote in message
...
I have a list of parameters I want 1 cell to calculate: e.g.
=IF(A1<0.3),1,IF(A10.3,A1<1.6),2,IF(A11.6,A1<3.1 ),3,....ETC. I KEEP
GETTING AN ERROR MESSAGE...IS THERE SOMETHING I AM MISSING?

Need help with IF nested formulas
 

You need to use some ANDs.

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

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

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

Need help with IF nested formulas
 

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



--

"Rick Rothstein (MVP - VB)" wrote in
message ...
You need to use some ANDs.

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

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

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