A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Excel IF/LARGE Function problem



 
 
Thread Tools Display Modes
  #11  
Old July 13th, 2004, 08:17 PM
Aladin Akyurek
external usenet poster
 
Posts: n/a
Default 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  
Old July 13th, 2004, 09:02 PM
andyp161
external usenet poster
 
Posts: n/a
Default 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  
Old July 14th, 2004, 05:03 PM
Leo Heuser
external usenet poster
 
Posts: n/a
Default 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  
Old July 14th, 2004, 10:43 PM
Ken Wright
external usenet poster
 
Posts: n/a
Default 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  
Old July 15th, 2004, 07:01 PM
Leo Heuser
external usenet poster
 
Posts: n/a
Default 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  
Old July 15th, 2004, 07:04 PM
Leo Heuser
external usenet poster
 
Posts: n/a
Default 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  
Old July 15th, 2004, 10:25 PM
hgrove
external usenet poster
 
Posts: n/a
Default 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  
Old July 15th, 2004, 10:28 PM
hgrove
external usenet poster
 
Posts: n/a
Default 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  
Old July 16th, 2004, 07:28 AM
Leo Heuser
external usenet poster
 
Posts: n/a
Default 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  
Old July 16th, 2004, 08:09 AM
Harlan Grove
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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 07:46 PM
GETPIVOTDATA function in Excel 2000 LS Worksheet Functions 1 February 26th, 2004 12:54 PM


All times are GMT +1. The time now is 03:02 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.