Problem using IF to create ranges

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

I am trying to use this formula to create a percentage
range:

=IF(b2>90,"91-100%",IF(b2>80,"81-90%",
IF(b2>70,"71-80%",IF(b2>60,"61-70%",IF(b2>50,"51-60%",IF
(b2>40,"41-50%",IF(b2>30,"31-40%",IF(b2>20,"21-30%",IF
(b2>10,"11-20%",IF(b2>0,"1-10%","None Scanned"))))))))))

Excel will only let me put in the following:

=IF(B2>90,"91-100%",IF(B2>80,"81-90%",
IF(B2>70,"71-80%",IF(B2>60,"61-70%",IF(B2>50,"51-60%",IF
(B2>40,"41-50%",IF(B2>30,"31-40%",IF(B2>20,"21-30%","None
Scanned"))))))))

This cuts off my last 2 options. Is there a way around
this? Using Microsoft Office 2000.

Thanks...
Chris
 
Hi Chris,

Look at the VLOOKUP() function. See Help for details

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
Hi Chris
7 nested functions are the maximum for Excel. Looking at your example I
would suggest the following:
1. Create a lookup table on a spearate sheet/range. Lets say you create
the following in the sheet 'lookup':
A B
0 1-10%
11 11-20%
...

2. Now you can use VLOOKUP:
=VLOOKUP(B2,'lookup'!$A$1:$B$10,2,1)

Frank
 
Would something like this work for you instead?

=10*INT(B2/10)+1 & "-" & 10*INT(B2/10)+10 & "%"
 
I am trying to use this formula to create a percentage
range:

=IF(b2>90,"91-100%",IF(b2>80,"81-90%",
IF(b2>70,"71-80%",IF(b2>60,"61-70%",IF(b2>50,"51-60%",IF
(b2>40,"41-50%",IF(b2>30,"31-40%",IF(b2>20,"21-30%",IF
(b2>10,"11-20%",IF(b2>0,"1-10%","None Scanned"))))))))))

Excel will only let me put in the following:

=IF(B2>90,"91-100%",IF(B2>80,"81-90%",
IF(B2>70,"71-80%",IF(B2>60,"61-70%",IF(B2>50,"51-60%",IF
(B2>40,"41-50%",IF(B2>30,"31-40%",IF(B2>20,"21-30%","None
Scanned"))))))))

This cuts off my last 2 options. Is there a way around
this? Using Microsoft Office 2000.

Thanks...
Chris
Click to expand...

In addition to Niek's suggestion of a LOOKUP function, you could also use a
formula:

=IF(B2=0,"None Scanned",FLOOR(B2-10^-10,10)+1&"-"&FLOOR(B2-10^-10,10)+10&"%")


--ron
 
This worked great. Not sure if you can help me take it a
step further, but ....

I am making a Map with MS Map in excel 2000. Here's what
I want to do...

I want to keep the value of zero as a seperate key color.
but the range of my key gives "0-9%" as one color and I
need 0 to be it;s own color.

I am using vlookup to use a range. For example, if the
value is 15%, then it calls "11-20%."

So I would like the key to read as follows (words in
brackets would be the colors).

[white] none
[first shade] 1-10%
[second shade] 11-20%
etc...

I want to be able to set the range values as well (1-10%,
11-20%) but all I see are the ability to make it either
equal number of values or a equal range based on my
numbers (i.e. .34-.45 based on my data) any suggestions
here? Can this be done?
 
Back
Top