Nested If statement -- please help :)

  • Thread starter Thread starter HS
  • Start date Start date
H

HS

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+
Click to expand...

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

thank you!!!
 
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.
 
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 -----
 
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 :)
 
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.
 
Hi

=IF(A1>19999999,"E >$20M",
IF(A1>9999999,"D - $10m - 20M",
IF(A1>4999999,"C - $5M-$10M",
IF(A1>999999,"B - $1M-$5m","A - <$1m"))))
 
Did you try the nested if formula I sent? If so, how does it not perform as
required?
 
Oops! looked and responded to this as if it was a response to my solution.
No offence intended.
 
Back
Top