Nested If statement -- please help :)
 

Hi, I am trying to create a nested if .. I'm guessing this is what I need but
it's not working

I am trying to categorize total fees

<$1,000,000 = A - <$1M
Between $1,000,000 and $5,000,000 = B - $1M-$5M
Between $5,000,000 and $10,000,000 = C - $5M-$10M
Between $10,000,000 and $20,000,000 = C - $10M-$20M
$20,000,000 = E - $20M+

I started with this but it's not working?
IF(I2<1000000,"A - <$1M",IF(I2999999 and <4999999,"B -
$1M-$5M",IF(I24999999 and <9999999,"$10M-$20M,0)))

thank you!!!

Nested If statement -- please help :)
 

You can't use Letters in your calculation. $10M will not work. You have to
put the correct number without the "M". You are using improper syntax for the
AND function Nd what row in column "A" or "B" are you subtracting from? You
need to use the column and row together like A3 or B45.
--
Dave Herard
MOS Master Instructor
Microsoft Certified Trainer
www.clicknlearnct.com


"HS" wrote:

Hi, I am trying to create a nested if .. I'm guessing this is what I need but
it's not working

I am trying to categorize total fees

<$1,000,000 = A - <$1M
Between $1,000,000 and $5,000,000 = B - $1M-$5M
Between $5,000,000 and $10,000,000 = C - $5M-$10M
Between $10,000,000 and $20,000,000 = C - $10M-$20M
$20,000,000 = E - $20M+

I started with this but it's not working?
IF(I2<1000000,"A - <$1M",IF(I2999999 and <4999999,"B -
$1M-$5M",IF(I24999999 and <9999999,"$10M-$20M,0)))

thank you!!!

Nested If statement -- please help :)
 

If you want to use an IF() expression, you could do something like this:

=if(A1<1000000, "A - <$1M",
if(A1<5000000, "B - $1M-$5M",
if(A1<10000000, "C - $5M-$10M",
if(A1<20000000, "D - $10M-$20M", "E - $20M+"))))

Note that it is unnecessary to use AND(). In fact, that often causes
problems at the boundaries. "if(A1<5000000" is the same as saying
"if(and(1000000<=A1,A1<5000000") because if A1 were less than $1M, the first
conditional expression would be true, and we would return category A. Also
note that we do not need an IF() expression for =$20M. It is implied by
the "value-if-false" result when "if(A1<20000000" is false.

Note: Your categories are ambiguous. For example, is $5M exactly in
category B or C? I used category A as model, assuming that category B is
really "$1M to <$5M".

There are a number of alternatives relying on match or lookup functions.
For example:

=lookup(A1,{0,1000000,5000000,10000000,20000000},{ "A","B","C","D","E"})

This form is especially advantageous when you have more than 8 categories.
In that case, an IF() formulation would exceed the nesting limit pre-2007
revisions of Excel.


----- original message -----

"HS" wrote in message
...
Hi, I am trying to create a nested if .. I'm guessing this is what I need
but
it's not working

I am trying to categorize total fees

<$1,000,000 = A - <$1M
Between $1,000,000 and $5,000,000 = B - $1M-$5M
Between $5,000,000 and $10,000,000 = C - $5M-$10M
Between $10,000,000 and $20,000,000 = C - $10M-$20M
$20,000,000 = E - $20M+

I started with this but it's not working?
IF(I2<1000000,"A - <$1M",IF(I2999999 and <4999999,"B -
$1M-$5M",IF(I24999999 and <9999999,"$10M-$20M,0)))

thank you!!!

Nested If statement -- please help :)
 

Thanks for responding Im not trying to calculate anything just hoping to look
at a value in Column I and then categorize it by placing the letter it's
easier to sort it ..

So, if the total fees in Column I are less than $1,000,000 then I want to
tag it in a category of <$1M? Can this be done?

thanks :)
"DJH6064" wrote:

You can't use Letters in your calculation. $10M will not work. You have to
put the correct number without the "M". You are using improper syntax for the
AND function Nd what row in column "A" or "B" are you subtracting from? You
need to use the column and row together like A3 or B45.
--
Dave Herard
MOS Master Instructor
Microsoft Certified Trainer
www.clicknlearnct.com


"HS" wrote:

Hi, I am trying to create a nested if .. I'm guessing this is what I need but
it's not working

I am trying to categorize total fees

<$1,000,000 = A - <$1M
Between $1,000,000 and $5,000,000 = B - $1M-$5M
Between $5,000,000 and $10,000,000 = C - $5M-$10M
Between $10,000,000 and $20,000,000 = C - $10M-$20M
$20,000,000 = E - $20M+

I started with this but it's not working?
IF(I2<1000000,"A - <$1M",IF(I2999999 and <4999999,"B -
$1M-$5M",IF(I24999999 and <9999999,"$10M-$20M,0)))

thank you!!!

Nested If statement -- please help :)
 

I think you are looking for:

=IF(I2<1000000,"A - <$1M",IF(I2<5000000,"B - $1M-$5M",
IF(I2<1000000,"C-$5M-$10M",IF(I2<20000000,"C-$10M-$20M","E-$20M+"))))

Hint: when you write formula think about overlap and gaps - how would your
starting formula cope with 999999.5?

I recommend against many levels of nested If. Vlookup is usually easier to
understand and maintain.

http://www.contextures.com/xlFunctions02.html

Your vlookup formula would look something like:

=VLOOKUP(I2,"yourVlookupRange",2)

The table would have values to look up in column 1 and lookup value in
column 2.

--
Steve

"HS" wrote in message
...
Hi, I am trying to create a nested if .. I'm guessing this is what I need
but
it's not working

I am trying to categorize total fees

<$1,000,000 = A - <$1M
Between $1,000,000 and $5,000,000 = B - $1M-$5M
Between $5,000,000 and $10,000,000 = C - $5M-$10M
Between $10,000,000 and $20,000,000 = C - $10M-$20M
$20,000,000 = E - $20M+

I started with this but it's not working?
IF(I2<1000000,"A - <$1M",IF(I2999999 and <4999999,"B -
$1M-$5M",IF(I24999999 and <9999999,"$10M-$20M,0)))

thank you!!!

Nested If statement -- please help :)
 

Hi

=IF(A119999999,"E $20M",
IF(A19999999,"D - $10m - 20M",
IF(A14999999,"C - $5M-$10M",
IF(A1999999,"B - $1M-$5m","A - <$1m"))))

--
Regards
Roger Govier

"HS" wrote in message
...
Hi, I am trying to create a nested if .. I'm guessing this is what I need
but
it's not working

I am trying to categorize total fees

<$1,000,000 = A - <$1M
Between $1,000,000 and $5,000,000 = B - $1M-$5M
Between $5,000,000 and $10,000,000 = C - $5M-$10M
Between $10,000,000 and $20,000,000 = C - $10M-$20M
$20,000,000 = E - $20M+

I started with this but it's not working?
IF(I2<1000000,"A - <$1M",IF(I2999999 and <4999999,"B -
$1M-$5M",IF(I24999999 and <9999999,"$10M-$20M,0)))

thank you!!!

Nested If statement -- please help :)
 

Did you try the nested if formula I sent? If so, how does it not perform as
required?

--
Steve

"HS" wrote in message
...
Thanks for responding Im not trying to calculate anything just hoping to
look
at a value in Column I and then categorize it by placing the letter it's
easier to sort it ..

So, if the total fees in Column I are less than $1,000,000 then I want to
tag it in a category of <$1M? Can this be done?

thanks :)
"DJH6064" wrote:

You can't use Letters in your calculation. $10M will not work. You have
to
put the correct number without the "M". You are using improper syntax for
the
AND function Nd what row in column "A" or "B" are you subtracting from?
You
need to use the column and row together like A3 or B45.
--
Dave Herard
MOS Master Instructor
Microsoft Certified Trainer
www.clicknlearnct.com


"HS" wrote:

Hi, I am trying to create a nested if .. I'm guessing this is what I
need but
it's not working

I am trying to categorize total fees

<$1,000,000 = A - <$1M
Between $1,000,000 and $5,000,000 = B - $1M-$5M
Between $5,000,000 and $10,000,000 = C - $5M-$10M
Between $10,000,000 and $20,000,000 = C - $10M-$20M
$20,000,000 = E - $20M+

I started with this but it's not working?
IF(I2<1000000,"A - <$1M",IF(I2999999 and <4999999,"B -
$1M-$5M",IF(I24999999 and <9999999,"$10M-$20M,0)))

thank you!!!

Nested If statement -- please help :)
 

Oops! looked and responded to this as if it was a response to my solution.
No offence intended.

--
Steve

"AltaEgo" <Somewhere@NotHere wrote in message
...
Did you try the nested if formula I sent? If so, how does it not perform
as required?

--
Steve

"HS" wrote in message
...
Thanks for responding Im not trying to calculate anything just hoping to
look
at a value in Column I and then categorize it by placing the letter it's
easier to sort it ..

So, if the total fees in Column I are less than $1,000,000 then I want to
tag it in a category of <$1M? Can this be done?

thanks :)
"DJH6064" wrote:

You can't use Letters in your calculation. $10M will not work. You have
to
put the correct number without the "M". You are using improper syntax
for the
AND function Nd what row in column "A" or "B" are you subtracting from?
You
need to use the column and row together like A3 or B45.
--
Dave Herard
MOS Master Instructor
Microsoft Certified Trainer
www.clicknlearnct.com


"HS" wrote:

Hi, I am trying to create a nested if .. I'm guessing this is what I
need but
it's not working

I am trying to categorize total fees

<$1,000,000 = A - <$1M
Between $1,000,000 and $5,000,000 = B - $1M-$5M
Between $5,000,000 and $10,000,000 = C - $5M-$10M
Between $10,000,000 and $20,000,000 = C - $10M-$20M
$20,000,000 = E - $20M+

I started with this but it's not working?
IF(I2<1000000,"A - <$1M",IF(I2999999 and <4999999,"B -
$1M-$5M",IF(I24999999 and <9999999,"$10M-$20M,0)))

thank you!!!

Nested If statement -- please help :)
 

Thank you -- The lookup works great!!! And, I learned something new :)

"JoeU2004" wrote:

If you want to use an IF() expression, you could do something like this:

=if(A1<1000000, "A - <$1M",
if(A1<5000000, "B - $1M-$5M",
if(A1<10000000, "C - $5M-$10M",
if(A1<20000000, "D - $10M-$20M", "E - $20M+"))))

Note that it is unnecessary to use AND(). In fact, that often causes
problems at the boundaries. "if(A1<5000000" is the same as saying
"if(and(1000000<=A1,A1<5000000") because if A1 were less than $1M, the first
conditional expression would be true, and we would return category A. Also
note that we do not need an IF() expression for =$20M. It is implied by
the "value-if-false" result when "if(A1<20000000" is false.

Note: Your categories are ambiguous. For example, is $5M exactly in
category B or C? I used category A as model, assuming that category B is
really "$1M to <$5M".

There are a number of alternatives relying on match or lookup functions.
For example:

=lookup(A1,{0,1000000,5000000,10000000,20000000},{ "A","B","C","D","E"})

This form is especially advantageous when you have more than 8 categories.
In that case, an IF() formulation would exceed the nesting limit pre-2007
revisions of Excel.


----- original message -----

"HS" wrote in message
...
Hi, I am trying to create a nested if .. I'm guessing this is what I need
but
it's not working

I am trying to categorize total fees

<$1,000,000 = A - <$1M
Between $1,000,000 and $5,000,000 = B - $1M-$5M
Between $5,000,000 and $10,000,000 = C - $5M-$10M
Between $10,000,000 and $20,000,000 = C - $10M-$20M
$20,000,000 = E - $20M+

I started with this but it's not working?
IF(I2<1000000,"A - <$1M",IF(I2999999 and <4999999,"B -
$1M-$5M",IF(I24999999 and <9999999,"$10M-$20M,0)))

thank you!!!