Nested If statement to find greater than but less than numbers

[Aaron]

Hi,

I had this formula that works well:

=IF(M20>=1.5,"3",IF(AND(M20>=1,M20<1.5),"2",IF(AND(M20>=0.5,M20<1),"1",IF(AND(M20>=0.25,M20<0.5),"0.5",IF(M20<=0.25,"0.5","error")))))

so I thought I will add another cell for it to check in the AND part so
the formula looked like this:

=IF(AND(M19>=1.5,Q19>=3),"3",IF(AND(M19>=1,M19<1.5,Q19>=2,Q19<3),"2",IF(AND(M19>=0.5,M19<1,Q19>=1,Q19<2),"1",IF(AND(M19>=0.25,M19<0.5,Q19>=0.5,Q19<1),"0.5",IF(AND(M19<=0.25,Q19<0.5),"0.5","error")))))

But now it just returns "error"

Have I dont something illegal here?

Why cant it just after looking at m19 cell, just look at q19 also
before displaying its result?

Cheers for advice,

Aaron.

 

[Ron Rosenfeld]

Hi,

I had this formula that works well:

=IF(M20>=1.5,"3",IF(AND(M20>=1,M20<1.5),"2",IF(AND(M20>=0.5,M20<1),"1",IF(AND(M20>=0.25,M20<0.5),"0.5",IF(M20<=0.25,"0.5","error")))))

so I thought I will add another cell for it to check in the AND part so
the formula looked like this:

=IF(AND(M19>=1.5,Q19>=3),"3",IF(AND(M19>=1,M19<1.5,Q19>=2,Q19<3),"2",IF(AND(M19>=0.5,M19<1,Q19>=1,Q19<2),"1",IF(AND(M19>=0.25,M19<0.5,Q19>=0.5,Q19<1),"0.5",IF(AND(M19<=0.25,Q19<0.5),"0.5","error")))))

But now it just returns "error"

Have I dont something illegal here?

Why cant it just after looking at m19 cell, just look at q19 also
before displaying its result?

Cheers for advice,

Aaron.

I presume it is returning the string "error" which you have as the final string
to return.

When you write "now it just returns "error"", what sorts of values did you
test?

Did you try, for example, M19=1 and Q19=2? With those values, your formula on
my worksheet returns 2.

And why do you make it so complicated?

For example, you have:

=IF(M20>=1.5,"3",
IF(AND(M20>=1,M20<1.5),"2",...

If you get past the first IF, why do you need to test for M20<1.5? If M20 were
not <1.5, then the first IF would test TRUE and the formula would give a "3".
There's no reason to test it again, and it makes your formula much more
confusing to read.

And are you certain you want to return your numbers as string values, rather
than numeric values?

Your first formula could be simplified:

=IF(M20>=1.5,"3",
IF(M20>=1,"2",
IF(M20>=0.5,"1",
IF(M20>=0.25,"0.5","error"))))

or, if you really want numeric returns and not strings:

=IF(M20>=1.5,3,
IF(M20>=1,2,
IF(M20>=0.5,1,
IF(M20>=0.25,0.5,"error"))))

Your second formula can probably be simplified, but you need to be clear in
your mind as to what you are testing for.



But your formulas are doing exactly what you have told them to do.
--ron

 

[Aaron]

Hi Ron,

With some lateral thinking based on your recommendations, I think I
have now sorted it thanks.

Aaron.

 

[Ron Rosenfeld]

Hi Ron,

With some lateral thinking based on your recommendations, I think I
have now sorted it thanks.

Aaron.

I'm glad to have pointed you in a productive direction. Thanks for the
feedback.


--ron