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