Re: Problem using IF to create ranges

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Chris (anonymous_at_discussions.microsoft.com)
Date: 02/11/04 

Date: Wed, 11 Feb 2004 09:37:39 -0800

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?

>-----Original Message-----
>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 ***/range. Lets
say you create
>the following in the *** '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
>
>Chris wrote:
>> 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
>
>
>.
>

Quantcast