# Nested If statement to find greater than but less than numbers

Aaron
Guest
Posts: n/a

 10th Nov 2006
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?

Aaron.

Ron Rosenfeld
Guest
Posts: n/a

 10th Nov 2006
On 9 Nov 2006 16:34:34 -0800, "Aaron" <(E-Mail Removed)> wrote:

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

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
Guest
Posts: n/a

 10th Nov 2006
Hi Ron,

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

Aaron.

Ron Rosenfeld wrote:
> On 9 Nov 2006 16:34:34 -0800, "Aaron" <(E-Mail Removed)> wrote:
>
> >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?
> >
> >
> >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
>
> 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

Ron Rosenfeld
Guest
Posts: n/a

 10th Nov 2006
On 9 Nov 2006 18:31:41 -0800, "Aaron" <(E-Mail Removed)> wrote:

>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

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Bec G (Oz) Microsoft Excel Misc 4 11th Feb 2010 07:35 AM charlie Microsoft Excel Worksheet Functions 5 26th Jun 2009 08:28 PM Workbook Microsoft Excel Worksheet Functions 4 4th Feb 2009 03:05 AM Tobey Microsoft Excel Programming 5 11th Jul 2008 07:50 PM steveo Microsoft Excel Misc 3 8th Jul 2006 02:04 AM

Features