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

x
?
Solved

Losing a max value

Posted on 2011-10-16
5
Medium Priority
?
166 Views
Last Modified: 2012-05-12
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
Comment
Question by:Gerald Connolly
  • 3
  • 2
5 Comments
 
LVL 50

Accepted Solution

by:
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

by:Gerald Connolly
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

by:barry houdini
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

by:Gerald Connolly
ID: 37014913
Thanks Barry that makes perfect sense.
0
 
LVL 17

Author Closing Comment

by:Gerald Connolly
ID: 37014915
Excellent and prompt response
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question