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