Nested IF(AND is not working

[Todd F.]

I searched & searched for an old post to cover this but nothing out there.

I have the following formula that is got an error in it:

=IF(I2<30, "< 30"), IF(AND(I2>=30, I2<=60),"31-60"), IF(AND(I2>60,
I2<=90),"61-90"), IF(I2>=91, "> 91")

my goal is tot have the following for statements placed in a cell via this
calculation to cover 4 possible variables.

if number is then or equal to 30 then tag "<30"
if number is 31 or greater and 60 or less then tag "31-60"
if number is greater then 60 but less then or equal to 90 then tag "61-90"
if number is greater then or equal to 91 then tag "91+"

I use to do these all the time what is up with this formula:

Thanks Todd Frisch [email protected]

[bj]

The only thing I see wrong is that you need 3 more parenthsis at the end of
the equation,

You could make it simpler
=if(I2<30,"<30,if(I2>90,">90",if(I2>60,"61-90","30-60")))
"Todd F." wrote:

> I searched & searched for an old post to cover this but nothing out there.
>
> I have the following formula that is got an error in it:
>
> =IF(I2<30, "< 30"), IF(AND(I2>=30, I2<=60),"31-60"), IF(AND(I2>60,
> I2<=90),"61-90"), IF(I2>=91, "> 91")
>
> my goal is tot have the following for statements placed in a cell via this
> calculation to cover 4 possible variables.
>
> if number is then or equal to 30 then tag "<30"
> if number is 31 or greater and 60 or less then tag "31-60"
> if number is greater then 60 but less then or equal to 90 then tag "61-90"
> if number is greater then or equal to 91 then tag "91+"
>
> I use to do these all the time what is up with this formula:
>
> Thanks Todd Frisch [email protected]
>
>

[garfield-n-odie]

=IF(I2="","",IF(I2<30,"<30",IF(I2<60,"31-60",IF(I2<90,"61-90","91+")))

Todd F. wrote:

> I searched & searched for an old post to cover this but nothing out there.
>
> I have the following formula that is got an error in it:
>
> =IF(I2<30, "< 30"), IF(AND(I2>=30, I2<=60),"31-60"), IF(AND(I2>60,
> I2<=90),"61-90"), IF(I2>=91, "> 91")
>
> my goal is tot have the following for statements placed in a cell via this
> calculation to cover 4 possible variables.
>
> if number is then or equal to 30 then tag "<30"
> if number is 31 or greater and 60 or less then tag "31-60"
> if number is greater then 60 but less then or equal to 90 then tag "61-90"
> if number is greater then or equal to 91 then tag "91+"
>
> I use to do these all the time what is up with this formula:
>
> Thanks Todd Frisch [email protected]
>
>

[Todd F.]

I did the parenthisis and tried several combinations - the darn thing will
not work.

Any thoughts on my formula and thanks for your formula but I would like to
solve my issue

"Todd F." wrote:

> I searched & searched for an old post to cover this but nothing out there.
>
> I have the following formula that is got an error in it:
>
> =IF(I2<30, "< 30"), IF(AND(I2>=30, I2<=60),"31-60"), IF(AND(I2>60,
> I2<=90),"61-90"), IF(I2>=91, "> 91")
>
> my goal is tot have the following for statements placed in a cell via this
> calculation to cover 4 possible variables.
>
> if number is then or equal to 30 then tag "<30"
> if number is 31 or greater and 60 or less then tag "31-60"
> if number is greater then 60 but less then or equal to 90 then tag "61-90"
> if number is greater then or equal to 91 then tag "91+"
>
> I use to do these all the time what is up with this formula:
>
> Thanks Todd Frisch [email protected]
>
>

[Todd F.]

i will lok at it tonight I pasted it in but formula is visibl ein cell and
will not calculate

why o why

"bj" wrote:

> The only thing I see wrong is that you need 3 more parenthsis at the end of
> the equation,
>
> You could make it simpler
> =if(I2<30,"<30,if(I2>90,">90",if(I2>60,"61-90","30-60")))
> "Todd F." wrote:
>
> > I searched & searched for an old post to cover this but nothing out there.
> >
> > I have the following formula that is got an error in it:
> >
> > =IF(I2<30, "< 30"), IF(AND(I2>=30, I2<=60),"31-60"), IF(AND(I2>60,
> > I2<=90),"61-90"), IF(I2>=91, "> 91")
> >
> > my goal is tot have the following for statements placed in a cell via this
> > calculation to cover 4 possible variables.
> >
> > if number is then or equal to 30 then tag "<30"
> > if number is 31 or greater and 60 or less then tag "31-60"
> > if number is greater then 60 but less then or equal to 90 then tag "61-90"
> > if number is greater then or equal to 91 then tag "91+"
> >
> > I use to do these all the time what is up with this formula:
> >
> > Thanks Todd Frisch [email protected]
> >
> >

[Bill Kuunders]

A few too many brackets
=IF(I2<30, "<30", IF(AND(I2>=30, I2<=60),"31-60",
IF(AND(I2>60,I2<=90),"61-90", IF(I2>=91, "> 91"))))
This will work

You can simplify.........no need for the AND functions

=IF(I2<30, "< 30", IF(I2<=60,"31-60", IF(I2<=90,"61-90", "> 91")))

You may want to change the class "<30" to "<=30" if you have the next class
from 31 to 60.
And show the last as ">90"

=IF(I2<=30, "<= 30", IF(I2<=60,"31-60", IF(I2<=90,"61-90", "> 90")))


--
Greetings from New Zealand
Bill K

"Todd F." <[email protected]> wrote in message
news:[email protected]...
>I searched & searched for an old post to cover this but nothing out there.
>
> I have the following formula that is got an error in it:
>
> =IF(I2<30, "< 30"), IF(AND(I2>=30, I2<=60),"31-60"), IF(AND(I2>60,
> I2<=90),"61-90"), IF(I2>=91, "> 91")
>
> my goal is tot have the following for statements placed in a cell via this
> calculation to cover 4 possible variables.
>
> if number is then or equal to 30 then tag "<30"
> if number is 31 or greater and 60 or less then tag "31-60"
> if number is greater then 60 but less then or equal to 90 then tag "61-90"
> if number is greater then or equal to 91 then tag "91+"
>
> I use to do these all the time what is up with this formula:
>
> Thanks Todd Frisch [email protected]
>
>

[Ron Rosenfeld]

On Thu, 23 Jun 2005 13:20:02 -0700, "Todd F." <[email protected]>
wrote:

>I searched & searched for an old post to cover this but nothing out there.
>
>I have the following formula that is got an error in it:
>
>=IF(I2<30, "< 30"), IF(AND(I2>=30, I2<=60),"31-60"), IF(AND(I2>60,
>I2<=90),"61-90"), IF(I2>=91, "> 91")
>
>my goal is tot have the following for statements placed in a cell via this
>calculation to cover 4 possible variables.
>
>if number is then or equal to 30 then tag "<30"
>if number is 31 or greater and 60 or less then tag "31-60"
>if number is greater then 60 but less then or equal to 90 then tag "61-90"
>if number is greater then or equal to 91 then tag "91+"
>
>I use to do these all the time what is up with this formula:
>
>Thanks Todd Frisch [email protected]
>

Try:

=VLOOKUP(A2,{0,"<30";31,"31-60";61,"61-90";91,"91+"},2)


--ron

[Niek Otten]

Come on, Todd,

There are spaces in your formula, the brackets don't pair, you put brackets
where they shouldn't be, you surely did better in the past.

Try

=IF(I2<30,"<
30",IF(AND(I2>=30,I2<=60),"31-60",IF(AND(I2>60,I2<=90),"61-90",IF(I2>=91,">
91"))))

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"Todd F." <[email protected]> wrote in message
news:[email protected]...
>I searched & searched for an old post to cover this but nothing out there.
>
> I have the following formula that is got an error in it:
>
> =IF(I2<30, "< 30"), IF(AND(I2>=30, I2<=60),"31-60"), IF(AND(I2>60,
> I2<=90),"61-90"), IF(I2>=91, "> 91")
>
> my goal is tot have the following for statements placed in a cell via this
> calculation to cover 4 possible variables.
>
> if number is then or equal to 30 then tag "<30"
> if number is 31 or greater and 60 or less then tag "31-60"
> if number is greater then 60 but less then or equal to 90 then tag "61-90"
> if number is greater then or equal to 91 then tag "91+"
>
> I use to do these all the time what is up with this formula:
>
> Thanks Todd Frisch [email protected]
>
>

[bj]

a lot of times when you paste a formula into a cell it initially thinks it is
text. I often just click in front of the "=" hit delete and enter. other
times I have to reformat the cell as general.


"Todd F." wrote:

> i will lok at it tonight I pasted it in but formula is visibl ein cell and
> will not calculate
>
> why o why
>
> "bj" wrote:
>
> > The only thing I see wrong is that you need 3 more parenthsis at the end of
> > the equation,
> >
> > You could make it simpler
> > =if(I2<30,"<30,if(I2>90,">90",if(I2>60,"61-90","30-60")))
> > "Todd F." wrote:
> >
> > > I searched & searched for an old post to cover this but nothing out there.
> > >
> > > I have the following formula that is got an error in it:
> > >
> > > =IF(I2<30, "< 30"), IF(AND(I2>=30, I2<=60),"31-60"), IF(AND(I2>60,
> > > I2<=90),"61-90"), IF(I2>=91, "> 91")
> > >
> > > my goal is tot have the following for statements placed in a cell via this
> > > calculation to cover 4 possible variables.
> > >
> > > if number is then or equal to 30 then tag "<30"
> > > if number is 31 or greater and 60 or less then tag "31-60"
> > > if number is greater then 60 but less then or equal to 90 then tag "61-90"
> > > if number is greater then or equal to 91 then tag "91+"
> > >
> > > I use to do these all the time what is up with this formula:
> > >
> > > Thanks Todd Frisch [email protected]
> > >
> > >

[Todd F.]

this is a very interesting formula , never thought of using vlookup which I
am a big fan of . do you feel like explaining this to me or directing me to a
place to read
about this.

thanks for the time


"Ron Rosenfeld" wrote:

> On Thu, 23 Jun 2005 13:20:02 -0700, "Todd F." <[email protected]>
> wrote:
>
> >I searched & searched for an old post to cover this but nothing out there.
> >
> >I have the following formula that is got an error in it:
> >
> >=IF(I2<30, "< 30"), IF(AND(I2>=30, I2<=60),"31-60"), IF(AND(I2>60,
> >I2<=90),"61-90"), IF(I2>=91, "> 91")
> >
> >my goal is tot have the following for statements placed in a cell via this
> >calculation to cover 4 possible variables.
> >
> >if number is then or equal to 30 then tag "<30"
> >if number is 31 or greater and 60 or less then tag "31-60"
> >if number is greater then 60 but less then or equal to 90 then tag "61-90"
> >if number is greater then or equal to 91 then tag "91+"
> >
> >I use to do these all the time what is up with this formula:
> >
> >Thanks Todd Frisch [email protected]
> >
>
> Try:
>
> =VLOOKUP(A2,{0,"<30";31,"31-60";61,"61-90";91,"91+"},2)
>
>
> --ron
>

[Ron Rosenfeld]

On Fri, 24 Jun 2005 05:33:04 -0700, "Todd F." <[email protected]>
wrote:

>this is a very interesting formula , never thought of using vlookup which I
>am a big fan of . do you feel like explaining this to me or directing me to a
>place to read
>about this.
>
>thanks for the time
>
>
>"Ron Rosenfeld" wrote:
>
>>
>> Try:
>>
>> =VLOOKUP(A2,{0,"<30";31,"31-60";61,"61-90";91,"91+"},2)
>>
>>
>> --ron
>>

Look at HELP for VLOOKUP.

The part of the formula above that is within the braces is what is called an
array constant. Commas separate columns and semicolons separate rows.

So A2 is your lookup_value. Your lookup_array could also be a range reference
looking like:

0 <30
31 31-60
61 61-90
91 91+

In, let us say, L1:M4.

The "2" at the end of the formula says to find the match in column 2.

So the formula looks for some value (A2) in the leftmost column of the table
that is either an exact match or, if an exact match is not found, the next
largest value that is less than lookup_value.

Since, for example, there is no exact match for '15', the largest value in the
table that is less than 15 is '0'; in column 2 of that row is the "<30" so
that's what gets returned.

Lookup tables are frequently much more flexible, and easier to modify, than
complicated IF statements.

If you set up a table as above some place, instead of using the array constant,
the formula could be rewritten as:

=VLOOKUP(A2,tbl,2)

or

=VLOOKUP(A2,L1:M4,2)


--ron

[Todd F.]

thanks you much I will study this in next few days and I hope to make use of it

"Ron Rosenfeld" wrote:

> On Fri, 24 Jun 2005 05:33:04 -0700, "Todd F." <[email protected]>
> wrote:
>
> >this is a very interesting formula , never thought of using vlookup which I
> >am a big fan of . do you feel like explaining this to me or directing me to a
> >place to read
> >about this.
> >
> >thanks for the time
> >
> >
> >"Ron Rosenfeld" wrote:
> >
> >>
> >> Try:
> >>
> >> =VLOOKUP(A2,{0,"<30";31,"31-60";61,"61-90";91,"91+"},2)
> >>
> >>
> >> --ron
> >>
>
> Look at HELP for VLOOKUP.
>
> The part of the formula above that is within the braces is what is called an
> array constant. Commas separate columns and semicolons separate rows.
>
> So A2 is your lookup_value. Your lookup_array could also be a range reference
> looking like:
>
> 0 <30
> 31 31-60
> 61 61-90
> 91 91+
>
> In, let us say, L1:M4.
>
> The "2" at the end of the formula says to find the match in column 2.
>
> So the formula looks for some value (A2) in the leftmost column of the table
> that is either an exact match or, if an exact match is not found, the next
> largest value that is less than lookup_value.
>
> Since, for example, there is no exact match for '15', the largest value in the
> table that is less than 15 is '0'; in column 2 of that row is the "<30" so
> that's what gets returned.
>
> Lookup tables are frequently much more flexible, and easier to modify, than
> complicated IF statements.
>
> If you set up a table as above some place, instead of using the array constant,
> the formula could be rewritten as:
>
> =VLOOKUP(A2,tbl,2)
>
> or
>
> =VLOOKUP(A2,L1:M4,2)
>
>
> --ron
>

[Todd F.]

how very true I have bene away from vba so long I am ashamed of my current
skills

also very sorry to see access to my old posts seems to have dried up.

I appreciate your effort - your repost worked

I think with this curren tjob i will have ample opportunity to get serious
about vba once and for all.

"Niek Otten" wrote:

> Come on, Todd,
>
> There are spaces in your formula, the brackets don't pair, you put brackets
> where they shouldn't be, you surely did better in the past.
>
> Try
>
> =IF(I2<30,"<
> 30",IF(AND(I2>=30,I2<=60),"31-60",IF(AND(I2>60,I2<=90),"61-90",IF(I2>=91,">
> 91"))))
>
> --
> Kind regards,
>
> Niek Otten
>
> Microsoft MVP - Excel
>
> "Todd F." <[email protected]> wrote in message
> news:[email protected]...
> >I searched & searched for an old post to cover this but nothing out there.
> >
> > I have the following formula that is got an error in it:
> >
> > =IF(I2<30, "< 30"), IF(AND(I2>=30, I2<=60),"31-60"), IF(AND(I2>60,
> > I2<=90),"61-90"), IF(I2>=91, "> 91")
> >
> > my goal is tot have the following for statements placed in a cell via this
> > calculation to cover 4 possible variables.
> >
> > if number is then or equal to 30 then tag "<30"
> > if number is 31 or greater and 60 or less then tag "31-60"
> > if number is greater then 60 but less then or equal to 90 then tag "61-90"
> > if number is greater then or equal to 91 then tag "91+"
> >
> > I use to do these all the time what is up with this formula:
> >
> > Thanks Todd Frisch [email protected]
> >
> >
>
>
>

[Ron Rosenfeld]

On Fri, 24 Jun 2005 13:05:02 -0700, "Todd F." <[email protected]>
wrote:

>thanks you much I will study this in next few days and I hope to make use of it

You're welcome. Post back if any problems.


--ron