a long thread of if-then statements

[rayjuan]

Hi. I'm using MS Excel Xp.
Here's my problem:
I have a large flatfile of our sales people and I need to do a pivot
table on them. but before that, I need to create a new column which
contains certain tiering of their earnings.

For example:

Name: Earnings Tier
Person A $5000 $0 - $10,000
Person B $34,500 $30,001 - $40,000
Person C $1M $990,000 - $1,000,000
Person D $2.5M $2,490,001 - $2,500,000

The column "Tier" is the one I need to create. How do I create a long
IF-THEN statement on this so that each sales person's earnings is
reflected/segmented into the tier? I believe there is a certain limit
on the number of IF-THEN statements that one can use, right?

Any help on the matter will be very much apprecited. Thanks!
By the way, the tiering is on a fixed increment of 10,000 units.

Thanks again!

RJ from the Philippines

 

[Jim]

Have alook at VLOOKUP, which will do what you want.

 

[Ragdyer]

If you're going from $0 to over $2,500,000 in $10,000 increments - then
you're talking about 250 tiers !!!
That's a heck of a lot to think about entering into one formula.

One option - is to create a list , perhaps in an out of the way location of
your WS, and then use one of the lookup functions to access the correct tier
from that list.
In my suggestion, even that list will use 500 rows to create.
Perhaps someone will come up with something simpler, maybe using the pivot
table itself to produce what your looking for.

Anyway, here's one approach:

Start this list using columns Y and Z to keep it out of the way.

Starting in Y1,
1
10000
10001
20000
20001
30000
30001
40000
40001
.... etc.

Starting in Z1,
0 - 10,000
0 - 10,000
10,001 - 20,000
10,001 - 20,000
20,001 - 30,000
20,001 - 30,000
30,001 - 40,000
30,001 - 40,000
40,001 - 50,000
.... etc.

Assume:
Name is column A - Earnings is column B - Tier is column C

In C2, enter this formula:
=VLOOKUP(B2,$Y$1:$Z$500,2)

Drag down to copy as needed.

Be advised that column B *MUST* be numbers !
*NO* using $2.5M !!!
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[Max]

As Jim has mentioned, VLOOKUP can achieve what you want

Here's some steps to ease you-in for a possible set-up to fit your specs

-----------
In Sheet1
-----------
Here's where we can set-up the reference table for the VLOOKUP
(I'd usually use a separate sheet for this)

Put in:

A1: 0
A2: 10000

Put in B1:
=IF(ISBLANK(A2),">"&TEXT(A1,"$#,##0"),TEXT(A1,"$#,##0")&"-"&TEXT(A2,"$#,##0"
))

Copy B1 down to B2

Now select A1:B2
Copy down to say, row301
(assuming highest tier value is >$3M)

Name the range A1:B301 as: TierTable

To name the range:

Select A1:B301

Click inside the namebox*, delete whatever's inside
(*the one with the drop-arrow just to the left of the formula bar)

Type in the namebox: TierTable
Press Enter

------------
In Sheet2
------------
Assuming (from your post):
col A is for "Names"
col B is for "Earnings"
col C is for "Tier"
with data starting from row2 down

Put in C2: =VLOOKUP(B2,TierTable,2,TRUE)
Copy C2 down col C to the last row of data

Col C will return the tier for the earnings in col B

(Btw, pl note that the earnings in col B have to be numbers,
e.g.: "3000" not "3K" or "2000000" not "2M")

 

[Norman Harker]

Hi Ray!

I might be wrong but I think that you'll get your tier using:

=ROUNDUP(A1,-4)/10000

ROUNDUP with -4 as the number of difits will round to the nearest
10000 and dividing by 10000 looks like giving you the tier where
(e.g.) 10000 is the top of a tier and (e.g.) 10001 is the start of the
next one.

But otherwise, I'd be going for a VLOOKUP approach

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[A.W.J. Ales]

I may be misunderstanding something, but if your intervals are 10000 fixed
you could (as far as i understand it) use for the lower bound :

Int(earnings/10000)*10000 and for the higher bound :
(Int(earnings/10000)+1)*10000

(For the person B this should give a lower bound of 30000 rather than 30001;
your lower bounds are not consistent ).

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *