If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Excel IF/LARGE Function problem
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 " wrote in message ... To clarify, the original numbers are to remain where they are; bonus points are to be inserted in the cells to the right of the four highest numbers. Regards --- Message posted from http://www.ExcelForum.com/ |
#12
|
|||
|
|||
Excel IF/LARGE Function problem
I don't think so, although my knowledge of Lookup is limited. My aim is
for this whole process to be automated, so that whenever points are awarded to employees (which of course must be done manually), the summary tables I have created do the rest. The formula proposed by Ken does this, except for the problem with equal scores as discussed above. Thanks for all your help. --- Message posted from http://www.ExcelForum.com/ |
#13
|
|||
|
|||
Excel IF/LARGE Function problem
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. -- Best Regards Leo Heuser Followup to newsgroup only please. "andyp161 " skrev i en meddelelse ... I don't think so, although my knowledge of Lookup is limited. My aim is for this whole process to be automated, so that whenever points are awarded to employees (which of course must be done manually), the summary tables I have created do the rest. The formula proposed by Ken does this, except for the problem with equal scores as discussed above. Thanks for all your help. --- Message posted from http://www.ExcelForum.com/ |
#14
|
|||
|
|||
Excel IF/LARGE Function problem
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 :-) ---------------------------------------------------------------------------- "Leo Heuser" wrote in message ... andy snip --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.719 / Virus Database: 475 - Release Date: 12/07/2004 |
#15
|
|||
|
|||
Excel IF/LARGE Function problem
Thanks Ken :-)
Please see my followup to my original posting. -- Best Regards Leo "Ken Wright" skrev i en meddelelse ... Nice one Leo - I like it all being in one. |
#16
|
|||
|
|||
Excel IF/LARGE Function problem
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 "Leo Heuser" skrev i en meddelelse ... 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. -- Best Regards Leo Heuser Followup to newsgroup only please. |
#17
|
|||
|
|||
Excel IF/LARGE Function problem
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" skrev ... 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$1 3, 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 ... Very brute force. Using your setup: names in A2:A201, scores in B2:B201, bonuses to go into C2:C201, and an arbitrary number of bonuses in descending order in col K beginning with K2. 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). 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's approach and make a 2-column table for bonuses with the first column calculated. Keeping col K as above, enter the following formulas in col J. J2: =MAX(B$2:B$201) J3 [*array* formula]: =MAX(IF(B$2:B$201J2,B$2:B$201)) Select J3 and fill down into J4:J7 (yes, J7). Then change the col C formula as follows. C2: =-LOOKUP(-B2,-$J$2:$K$7) Select C2 and fill down into C3:C201. No thanks necessary for the feedback. --- Message posted from http://www.ExcelForum.com/ |
#18
|
|||
|
|||
Excel IF/LARGE Function problem
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))) --- Message posted from http://www.ExcelForum.com/ |
#19
|
|||
|
|||
Excel IF/LARGE Function problem
"hgrove " skrev i en meddelelse
... 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 |
#20
|
|||
|
|||
Excel IF/LARGE Function problem
"Leo Heuser" wrote...
"hgrove " skrev i en meddelelse fixing my typo C2: =INDEX(Bonus,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. ? 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. 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. 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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Problem with Office Excel | aphmhidc | Worksheet Functions | 1 | June 3rd, 2004 03:41 PM |
Mouse Wheel Function Lost in Excel VBA 2003 | Sid | Setting up and Configuration | 2 | April 10th, 2004 02:53 AM |
Excel XP Nested If Function Problem | DaffyD® | Worksheet Functions | 2 | March 6th, 2004 06:46 PM |
GETPIVOTDATA function in Excel 2000 | LS | Worksheet Functions | 1 | February 26th, 2004 11:54 AM |