• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 171
  • Last Modified:

Losing a max value

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
Gerald Connolly
Asked:
Gerald Connolly
  • 3
  • 2
2 Solutions
 
barry houdiniCommented:
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
 
Gerald ConnollyAuthor Commented:
@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
 
barry houdiniCommented:
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
 
Gerald ConnollyAuthor Commented:
Thanks Barry that makes perfect sense.
0
 
Gerald ConnollyAuthor Commented:
Excellent and prompt response
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now