+ Reply to Thread
Results 1 to 10 of 10

Min values from multiple sets

Hybrid View

  1. 03-07-2009, 02:20 PM #1
    pbeauchemin
    pbeauchemin is offline
    Registered User
    Join Date
    03-05-2009
    Location
    Alberta, Canada
    MS-Off Ver
    Excel 2003
    Posts
    9

    Min values from multiple sets

    I am attaching a worksheet for which I need to calculate "National Points". I need to select 5 lowest values from "Tech" and "Speed" (Row 3 headings) events to the left of the totals column but a minimum of three "Tech" values need to be used. I have used the min, small 2, small 3 for the "Tech" set then used an IF statement for the fourth value. The formula returns the correct sum for values up to the fourth value. The problem comes on the fifth value. It seems to depend on if the 4th and 5th value come from the "Tech" events or "Speed" events. I have calculated the values manually and am unable to get them to correspond all the time. Can anyone help?
    Attached Files Attached Files
    Last edited by pbeauchemin; 03-08-2009 at 07:25 PM.
    Reply With QuoteRegister To Reply
  2. 03-07-2009, 09:07 PM #2
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Min values from multiple sets

    Can you show some expected results and how you would of got them?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  3. 03-07-2009, 09:25 PM #3
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,699

    Re: Min values from multiple sets

    If you are taking the 3 lowest "Tech" values then presumably to get the next 2 you can take the lowest 2 "Speed" values....and the 4th and 5th smallest "Tech" values, all together, and the take the lowest 2 from that group.

    Assuming your are ignoring zero values perhaps that would be something like this to get your 4th value

    =SMALL(CHOOSE({1,2},SMALL(IF($A$3:$AD$3="Tech",IF($A4:$AD4<>0,$A4:$AD4)),{4,5}),SMALL(IF($A$3:$AD$3="Speed",IF($A4:$AD4<>0,$A4:$AD4)),{1,2})),1)

    confirmed with CTRL+SHIFT+ENTER

    and then the same again but with a 2 at the end instead of a 1 for value 5.....
    Reply With QuoteRegister To Reply
  4. 03-08-2009, 12:03 AM #4
    pbeauchemin
    pbeauchemin is offline
    Registered User
    Join Date
    03-05-2009
    Location
    Alberta, Canada
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Min values from multiple sets

    I have attached a new worksheet with manual calculations of the first 15 expected values in Col Y. As you can see the majority of the results in Col X are correct. I have determined that the problem comes in when all results come from the "Tech" category. I can't get the formula that I have tried to generate the 5th highest value.
    Reply With QuoteRegister To Reply
  5. 03-08-2009, 01:19 AM #5
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Min values from multiple sets

    Maybe my logic is wrong, but I get some different numbers than you with my formula, which is:

    =SUM(SMALL(IF($F$3:$W$3="Tech",F4:W4),{1,2,3}))+IF(SMALL(IF($F$3:$W$3="Speed",F4:W4),1)<SMALL(IF($F$3:$W$3="Tech",F4:W4),4),SMALL(IF($F$3:$W$3="Speed",F4:W4),1),SMALL(IF($F$3:$W$3="Tech",F4:W4),4))+IF(SMALL(IF($F$3:$W$3="Speed",F4:W4),2)<SMALL(IF($F$3:$W$3="Tech",F4:W4),5),SMALL(IF($F$3:$W$3="Speed",F4:W4),2),SMALL(IF($F$3:$W$3="Tech",F4:W4),5))
    confirmed with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear around the formula. Then copied down.

    My logic is:

    Sum the minimum 3 values corresponding to "Tech", then check the minimum value of "Speed" items... if that is less than the 4th smallest "Tech" value, then add the min "Speed" value otherwise add the 4th smallest "Tech" value, then check the 2nd smallest "Speed" value and compare to the 5th smallest "Tech" value and add the smaller of the 2 to the other 4 values retrieved.

    If that is still wrong... then you will need to clarify...why not.
    Last edited by NBVC; 03-08-2009 at 01:24 AM.
    Reply With QuoteRegister To Reply
  6. 03-08-2009, 02:01 AM #6
    pbeauchemin
    pbeauchemin is offline
    Registered User
    Join Date
    03-05-2009
    Location
    Alberta, Canada
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Min values from multiple sets

    I am trying your formula but am getting "#VALUE" back. The values in Col "Y" are the correct values (I calculated them manually). The values in Col "X" are what I get with my formula. The logic I am trying to use seems to be the same as yours.
    Reply With QuoteRegister To Reply
  7. 03-08-2009, 07:53 AM #7
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Min values from multiple sets

    after you enter ther formula, you have to hold the Ctrl+Shift keys down and then press Enter.. you will see curly brackets appear. Then copy down.
    Reply With QuoteRegister To Reply
  8. 03-08-2009, 03:55 PM #8
    pbeauchemin
    pbeauchemin is offline
    Registered User
    Join Date
    03-05-2009
    Location
    Alberta, Canada
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Min values from multiple sets

    I have tried the formula and it returns some of the correct values. Cols O, P & Q are should not be inclued in the search array. The formula works if none of the events are "Speed" events or if the 4th and 5th largest values are speed events. If only the 4th or only the 5th largest values are "Speed" events, then the formula does not return the correct sum.
    Reply With QuoteRegister To Reply
  9. 03-08-2009, 04:24 PM #9
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,699

    Re: Min values from multiple sets

    You could try this formula in row 4 copied down

    =SUM(SMALL(IF(F$3:W$3="Tech",IF(F$1:W$1<>"Whist",F4:W4)),{1,2,3}),SMALL(CHOOSE({1,2,3,4},SMALL(IF(F$3:W$3="Tech",IF(F$1:W$1<>"Whist",F4:W4)),4),SMALL(IF(F$3:W$3="Tech",IF(F$1:W$1<>"Whist",F4:W4)),5),SMALL(IF(F$3:W$3="Speed",IF(F$1:W$1<>"Whist",F4:W4)),1),SMALL(IF(F$3:W$3="Speed",IF(F$1:W$1<>"Whist",F4:W4)),2)),{1,2}))

    needs to be confirmed with CTRL+SHIFT+ENTER

    I put it in column A1 on the attached....
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 03-08-2009, 06:23 PM #10
    pbeauchemin
    pbeauchemin is offline
    Registered User
    Join Date
    03-05-2009
    Location
    Alberta, Canada
    MS-Off Ver
    Excel 2003
    Posts
    9

    Smile Re: Min values from multiple sets

    Thank you DDLL. I have inserted the formula and the correct values return. I have extended the formula with other parameters and it works as well. I wish I could follow all of the logic as well as the formula so that if I run across something similar again I would be able to recreate it. Thanks for all your help.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1