[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Losing a max value

Posted on 2011-10-16
Medium Priority
166 Views
I have a workbook that currently totals up to 5 riders from a list of 35, now i want to choose only 4 from the 5 losing the maximum value but i cannot work out how to do that in this case, obviously its easy to select the maximum value from a list using MAX or LARGE, but in this case i can't work out how to specify the selection range or is this not possible, and i will have to revert to doing it in VBA?
Team-Race.bmp
0
Question by:Gerald Connolly
• 3
• 2

LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 36976971
Hello connollyq,

Try changing the formula to this "array formula" in G49

=IF(\$C49="","",SUM(SMALL(IF(\$C\$8:\$C\$42=\$C49,G\$8:G\$42),{1,2,3,4})))

confirm with CTRL+SHIFT+ENTER then copy across and down

To confirm with CTRL+SHIFT+ENTER put the formula in G49, press F2 key to select formula then hold down CTRL and SHIFT keys and press ENTER. If done correctly then curly braces will appear around the formula in the formula bar

regards, barry
0

LVL 17

Author Comment

ID: 37011745
@barryhoudini, great that does the trick,

although it should have been h49 and it looks like this =IF(\$C49="","",SUM(SMALL(IF(\$C\$8:\$C\$42=\$C49,H\$8:H\$42),{1,2,3,4})))

Can you explain how it works please?
0

LVL 50

Assisted Solution

barry houdini earned 2000 total points
ID: 37013960
This part...

=IF(\$C\$8:\$C\$42=\$C49,H\$8:H\$42)

returns an "array" of values including all the H8:H42 values on the same row where C8:C42 matches C49 (FALSE otherwise), i.e. all the scores for that team

That can then go inside a SMALL function so

=SMALL(IF(\$C\$8:\$C\$42=\$C49,H\$8:H\$42),1)

would return the smallest of those values

that can be extended to return an array of the smallest 4 by using {1,2,3,4} in place of 1

=SMALL(IF(\$C\$8:\$C\$42=\$C49,H\$8:H\$42),{1,2,3,4})

now SUM sums that array to give you the sum of the 4 smallest values on matching rows, i.e. for each specific team

regards, barry
0

LVL 17

Author Comment

ID: 37014913
Thanks Barry that makes perfect sense.
0

LVL 17

Author Closing Comment

ID: 37014915
Excellent and prompt response
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
###### Suggested Courses
Course of the Month20 days, 14 hours left to enroll