Excel IF/LARGE Function problem

[andyp161]

An incentive scheme at work allocates points to employees and the thre
highest earners per week receive bonus points. Therefore, I want exce
to locate the three higest points in the rows of one column and inser
the corresponding bonus points in the same row of the adjacent cell.
I've tried combining the IF and Large functions to no avail.
Many thank

 

[Ken Wright]

One way:-

With labels in A1:B1, names in A2:A30, and scores in B2:B30, and the three
bonuses in the range G1:G3, highest in G1, in cell B2 put the following formula
and then copy down to B30

=IF(ISERROR(MATCH(B2,LARGE($B$2:$B$30,{1,2,3}),0)),"",CHOOSE(MATCH(B2,LARGE($B$2
:$B$30,{1,2,3}),0),$G$1,$G$2,$G$3))

 

[Aladin Akyurek]

See:

http://216.92.17.166/board2/viewtopic.php?t=69970

 

[andyp161]

Ken, this works brilliantly except for one thing - if, for example, tw
people have earned the highest number of points, they should bot
receive the highest bonus points, and similarly for 2nd, 3rd an
fourth; with the formula you kindly put forward, only for bonus point
are matched in total?

Kind regard

 

[Ken Wright]

That gets somewhat harder, because where do you stop? Is the bonus money
unlimited? If 4 people match the highest score, 2 people match the second
highest, and 3 people match the third highest score, do you give away 4 x Top
prize, 2 x 2nd prize and 3 x 3rd prize?

 

[andyp161]

Thanks for your response Ken. The answer, unfortunately, is yes - th
top bonus is awarded to however many people receive the highest points
the second highest bonus is awarded to however many people receive th
second highest points, and so forth. Can this be done? Although thi
might seem strange, the way the scheme is set up, it is very unlikel
that out of more than 200 hundred employees, more than 2 will receiv
equal points...

Many thank

 

[Aladin Akyurek]

The formula system I quoted adjusts Top N dynamically..

 

[andyp161]

Aladin, thanks for your help. Unfortunately, I cannot see how thi
formula will work?? I want excel to find the cells with the highest
numbers, where equal numbers count as one bonus level, and then inser
the bonus points as follows 1st=500, 2nd=250, 3rd=125 and 4th=75. Fro
my understanding of the formula you quoted, the nth largest numbers ar
extracted, entered in specified cells, and, similarly, labels ar
extracted and entered in specified cells.

Regard

 

[andyp161]

To clarify, the original numbers are to remain where they are; bonu
points are to be inserted in the cells to the right of the four highes
numbers.

Regard

 

[andyp161]

Is the above possible??

Regard

 

[Aladin Akyurek]

Does that not mean that you need a lookup table of scores and bonus points?
After determining the Top N list of names and their scores, you can lookup
the bonus points associated with the scores from the lookup table, using a
VLOOKUP() formula.

 

[andyp161]

I don't think so, although my knowledge of Lookup is limited. My aim i
for this whole process to be automated, so that whenever points ar
awarded to employees (which of course must be done manually), th
summary tables I have created do the rest. The formula proposed by Ke
does this, except for the problem with equal scores as discusse
above.

Thanks for all your help

 

[Leo Heuser]

andy

For only the top 3 (which is what you wanted in your original posting),
this formula will do the job.
(I would hate to make the formula for the top 6
It will work no matter how many duplicates are present,
but B2:B13 must contain at least 3 different numbers.
12,12,12,12,12,12,12,7,7,7,7,5 is OK, but
12,12,12,12,12,12,12,7,7,7,7,7 is not (only 2 different
numbers). C2:C13 will display the #NUM! error, if
less than 3 different numbers are present in B2:B13.


Names in A2:A13
Scores in B2:B13
Bonus in G2:G4

In C2 enter

=(B2=MAX($B$2:$B$13))*$G$2+(B2=LARGE($B$2:$B$13,
COUNTIF($B$2:$B$13,MAX($B$2:$B$13))+1))*$G$3+
(B2=LARGE($B$2:$B$13,COUNTIF($B$2:$B$13,
LARGE($B$2:$B$13,COUNTIF($B$2:$B$13,MAX($B$2:$B$13))+1))+
COUNTIF($B$2:$B$13,MAX($B$2:$B$13))+1))*$G$4

Copy C2 down to C13.

 

[Ken Wright]

Nice one Leo - I like it all being in one. I had gotten there but had to use a
helper table though the data in the helper table may be of use to have as well,
so I'll post it anyway:-

Labels in A1:C1 Name/Scores/Bonus
Names in A2:A30
Scores in B2:B30

Bonus values in G1:G3, largest in G1

E1 =LARGE(B2:B30,1) Largest value
E2 =LARGE(B2:B30,SUM(F1:F2)) 2nd largest value
E3 =LARGE(B2:B30,SUM(F1:F3)) 3rd largest value

F1 =COUNTIF(B2:B30,LARGE(B2:B30,1)) gives number of values
equalling largest
F2 =COUNTIF(B2:B30,LARGE(B2:B30,F1+1)) gives number of values equalling
2nd largest
F3 =COUNTIF(B2:B30,LARGE(B2:B30,F1+F2+1)) gives number of values equalling
3rd largest

C2 =IF(ISNA(VLOOKUP(B2,$E$1:$G$3,3,0)),"",VLOOKUP(B2,$E$1:$G$3,3,0)) and copy
down

This simply creates a table of the top 3 values regardless of how many
duplicates, and then just uses VLOOKUP against that table to match each score
against a bonus if applicable. Reasonably easy to scale up if need be as well.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

andy

<snip>

 

[Leo Heuser]

Thanks Ken

Please see my followup to my original posting.

 

[Leo Heuser]

For a more systematic approach, where it's fairly easy to
make e.g. a top 10 list try this setup:

Names in A2:A300
Scores in B2:B300
Bonuses in K2:K??

Formula in e.g. C2:C300

1. Name the scores range (B2:B300) "Rng" without quotes)
(Select the range and use the name box (left of the formula bar)
2. Choose Insert > Name > Define and define the following names:

Name Formula
N_1 =LARGE(Rng,1)
N_2 =LARGE(Rng,SUM((Rng=N_1)+0)+1)
N_3 =LARGE(Rng,SUM((Rng=N_1)+(Rng=N_2))+1)
N_4 =LARGE(Rng,SUM((Rng=N_1)+(Rng=N_2)+(Rng=N_3))+1)
N_5 =LARGE(Rng,SUM((Rng=N_1)+(Rng=N_2)+(Rng=N_3)+(Rng=N_4)+1)
etc.

The above is used for finding up to the 5 topmost (duplicates included)

In C2 enter:

For a top 1 list:
=(B2=N_1)*$K$2

For a top 2 list:
=(B2=N_1)*$K$2+(B2=N_2)*$K$3

For a top 3 list:
=(B2=N_1)*$K$2+(B2=N_2)*$K$3+(B2=N_3)*$K$4

For a top 4 list:
=(B2=N_1)*$K$2+(B2=N_2)*$K$3+(B2=N_3)*$K$4+(B2=N_4)*$K$5

For a top 5 list:
=(B2=N_1)*$K$2+(B2=N_2)*$K$3+(B2=N_3)*$K$4+(B2=N_4)*$K$5+(B2=N_5)*$K$6


Positions not used will display as 0 (zero)

Copy C2 to C300 with the fill handle (the little square in the lower
right corner of the cell)

LeoH

 

[hgrove]

Leo Heuser wrote...

For a more systematic approach, where it's fairly easy to make
e.g. a top 10 list try this setup:

Names in A2:A300
Scores in B2:B300
Bonuses in K2:K??

Formula in e.g. C2:C300 ...
For a top 5 list:
=(B2=N_1)*$K$2+(B2=N_2)*$K$3+(B2=N_3)*$K$4
+(B2=N_4)*$K$5+(B2=N_5)*$K$6 ...
"Leo Heuser" <[email protected]> skrev ...

...

Very brute force.

Using your setup: names in A2:A201, scores in B2:B201, bonuses to g
into C2:C201, and an arbitrary number of bonuses in descending order i
col K beginning with K2. If there were 5 bonus levels, enter th
bonuses in descending order in K2:K6 and enter 0 in K7. Name K2:K
Bonus (it needs to include the 0 in K7). Enter the following formula.

C2:
=INDEX(Bonust,MIN(SUMPRODUCT((B$2:B$201>=B2)
/COUNTIF(B$2:B$201,B$2:B$201)),ROWS(Bonus)))

Select C2 and fill down into C3:C201.

Even so, this is recalc-inefficient. Better to use a variation on Ken'
approach and make a 2-column table for bonuses with the first colum
calculated. Keeping col K as above, enter the following formulas in co
J.

J2:
=MAX(B$2:B$201)

J3 [*array* formula]:
=MAX(IF(B$2:B$201<J2,B$2:B$201))

Select J3 and fill down into J4:J7 (yes, J7). Then change the col
formula as follows.

C2:
=-LOOKUP(-B2,-$J$2:$K$7)

Select C2 and fill down into C3:C201.

No thanks necessary for the feedback

 

[hgrove]

hgrove wrote...
...

C2:
=INDEX(Bonust,MIN(SUMPRODUCT((B$2:B$201>=B2)
/COUNTIF(B$2:B$201,B$2:B$201)),ROWS(Bonus)))

Select C2 and fill down into C3:C201.

...

Typo. Change 'Bonust' to 'Bonus', so

C2:
=INDEX(Bonus,MIN(SUMPRODUCT((B$2:B$201>=B2)
/COUNTIF(B$2:B$201,B$2:B$201)),ROWS(Bonus)))

 

[Leo Heuser]

C2:
=INDEX(Bonust,MIN(SUMPRODUCT((B$2:B$201>=B2)
/COUNTIF(B$2:B$201,B$2:B$201)),ROWS(Bonus)))

Select C2 and fill down into C3:C201.

The OP may find your formula a bit too generous, since
everybody gets a bonus.

Even so, this is recalc-inefficient. Better to use a variation on Ken's
approach and make a 2-column table for bonuses with the first column
calculated.

I agree with the 2-column approach, but my understanding is, that the OP
wants a one-column-solution.

No thanks necessary for the feedback.

Since you insist:
No-thanks for the feedback.

LeoH

 

[Harlan Grove]

"hgrove >" <<[email protected]> skrev i en meddelelse

fixing my typo

The OP may find your formula a bit too generous, since
everybody gets a bonus.

?

Using a 20-row sample data range in A2:B21 and the following Bonus range
*INCLUDING* the zero value as the final entry,

1000
500
250
125
62.5
0

and changing my cell C2 formula to

=INDEX(Bonus,MIN(SUMPRODUCT((B$2:B$21>=B2)
/COUNTIF(B$2:B$21,B$2:B$21)),ROWS(Bonus)))

the sample data plus the column of bonuses comes out as follows in my
system.

AA__10_____0
AB__16___125
AC__12_____0
AD___5_____0
AE__18___500
AF__19__1000
AG__11_____0
AH__17___250
AI__12_____0
AJ__17___250
AK___3_____0
AL__19__1000
AM__18___500
AN__18___500
AO___9_____0
AP___1_____0
AQ___8_____0
AR__14____62.5
AS___9_____0
AT___9_____0

Did you overlook this piece of my message:

"If there were 5 bonus levels, enter the bonuses in descending
order in K2:K6 and enter 0 in K7. Name K2:K7 Bonus (it needs to
include the 0 in K7)."

Refer back to It's the second
sentence in my second paragraph.

I agree with the 2-column approach, but my understanding is, that the OP
wants a one-column-solution.

As soon as the OP sees how long Excel takes to recalculate using 1-column
formulas, s/he may very well change his/her mind.

No-thanks for the feedback.

Fine, but do try not to misread other people's posts. If you were to try
exercising greater care in your reading, you might just possibly learn
something.