Losing a max value

Posted on 2011-10-16
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?
Question by:Gerald Connolly
    LVL 50

    Accepted Solution

    Hello connollyq,

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


    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
    LVL 16

    Author Comment

    by:Gerald Connolly
    @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?
    LVL 50

    Assisted Solution

    by:barry houdini
    This part...


    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


    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


    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
    LVL 16

    Author Comment

    by:Gerald Connolly
    Thanks Barry that makes perfect sense.
    LVL 16

    Author Closing Comment

    by:Gerald Connolly
    Excellent and prompt response

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now