Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to sum 'n' largest values *not* in an array?
Hello,
I'm trying to find a way to take the high four scores from a league occurring over the spring/summer. Six total scores are possible, one event is mandatory, so I need the high three from the remaining five events. Clear as mud? ;) If the scores were simply one numeric value, I think I could make it work using 'sum' and 'large', judging by what I see in the help files. Therein lies the problem. The scores are recorded in a format similar to '595-30X', where the total points is '595', and '30X' is a tie-breaker value (i.e. if two people have the same number of points, but one has more 'X's, the higher X-count prevails). In order to make the formulas a little simpler, I've broken the scores apart with '595' in one column, and '30' in the one immediately adjacent to it. It looks close enough that a non-computer savvy person looking at it will recognize the scores for what they are, and makes adding up the points, adding up the X-count, and then concatenating the two back into a final cell in the format 'nnnn-xxx' relatively simple. Except... it makes it so the row for a given competitor looks like '585','20','592','20','595','30','599','38','0','0 ' (assuming competitor makes 4 out of the 5 qualifiers but misses the last one) How do I sum the high 3 values from '585','592','595','599','0' when they aren't in adjacent cells which I can give as an array value? Do I have to copy the values to some hidden cells or another sheet and do the array calcs on those (now adjacent) values? Is there a way to do this directly with out copying the values somewhere else? TIA, Monte |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to sum 'n' largest values *not* in an array?
'585','20','592','20','595','30','599','38','0',' 0'
What's the "rule" that differentiates the "X" values from the scores? Just looking at the values, the scores are significantly higher than the "X" values. If you can define a "rule" to separate the 2 then this can be done with a relatively simple formula, otherwise, it'll be slightly more complicated. For example, sum the highest 3 values that are greater than 50 (highest possible "X" value ???). -- Biff Microsoft Excel MVP "memilanuk" wrote in message ... Hello, I'm trying to find a way to take the high four scores from a league occurring over the spring/summer. Six total scores are possible, one event is mandatory, so I need the high three from the remaining five events. Clear as mud? ;) If the scores were simply one numeric value, I think I could make it work using 'sum' and 'large', judging by what I see in the help files. Therein lies the problem. The scores are recorded in a format similar to '595-30X', where the total points is '595', and '30X' is a tie-breaker value (i.e. if two people have the same number of points, but one has more 'X's, the higher X-count prevails). In order to make the formulas a little simpler, I've broken the scores apart with '595' in one column, and '30' in the one immediately adjacent to it. It looks close enough that a non-computer savvy person looking at it will recognize the scores for what they are, and makes adding up the points, adding up the X-count, and then concatenating the two back into a final cell in the format 'nnnn-xxx' relatively simple. Except... it makes it so the row for a given competitor looks like '585','20','592','20','595','30','599','38','0','0 ' (assuming competitor makes 4 out of the 5 qualifiers but misses the last one) How do I sum the high 3 values from '585','592','595','599','0' when they aren't in adjacent cells which I can give as an array value? Do I have to copy the values to some hidden cells or another sheet and do the array calcs on those (now adjacent) values? Is there a way to do this directly with out copying the values somewhere else? TIA, Monte |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to sum 'n' largest values *not* in an array?
On May 10, 10:46*am, "T. Valko" wrote:
'585','20','592','20','595','30','599','38','0',' 0' What's the "rule" that differentiates the "X" values from the scores? Just looking at the values, the scores are significantly higher than the "X" values. If you can define a "rule" to separate the 2 then this can be done with a relatively simple formula, otherwise, it'll be slightly more complicated. For example, sum the highest 3 values that are greater than 50 (highest possible "X" value ???). The scores are from a bullseye type target with a decimal (10,9,8,7,6,5) scoring system. The highest possible value for any individual shot is '10'. Inside the 10-ring is another scoring ring, the 'X' ring. Any hit in the X ring still counts as 10 points, but also counts as a tie-breaker. So ten shots all in the 10 ring but with only seven inside the X ring would be noted as '100-7X'. A 100-1X beats a 99-9X, because the highest point value wins first, with the X-count only coming into play in the event of a tie such as a 100-7X vs a 100-5X. Several individual stages add up to a daily aggregate like the '595-30X' mentioned earlier. The high three daily aggs out of five possible events (competitors may attend any or all of these qualifiers) are added up, plus the daily score from the one required event, and forms a grand aggregate for league champion, etc. Don't know if that makes it any clearer or not ;) Its something that is relatively easy, but time consuming, to do by way of Mk1 Mod0 eyeball (the sorting/filtering) but a bit more challenging to make happen in Excel (so far). Thanks, Monte |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to sum 'n' largest values *not* in an array?
If I understand you completely, I don't see your problem!
Since your X scores will *never* be even close to being the largest value, just include those cells in the Large() function ... they'll *never* enter into the calculation. Say your example data was in D2 to M2. Try this: =SUM(LARGE(D2:M2,{1,2,3})) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "memilanuk" wrote in message ... On May 10, 10:46 am, "T. Valko" wrote: '585','20','592','20','595','30','599','38','0',' 0' What's the "rule" that differentiates the "X" values from the scores? Just looking at the values, the scores are significantly higher than the "X" values. If you can define a "rule" to separate the 2 then this can be done with a relatively simple formula, otherwise, it'll be slightly more complicated. For example, sum the highest 3 values that are greater than 50 (highest possible "X" value ???). The scores are from a bullseye type target with a decimal (10,9,8,7,6,5) scoring system. The highest possible value for any individual shot is '10'. Inside the 10-ring is another scoring ring, the 'X' ring. Any hit in the X ring still counts as 10 points, but also counts as a tie-breaker. So ten shots all in the 10 ring but with only seven inside the X ring would be noted as '100-7X'. A 100-1X beats a 99-9X, because the highest point value wins first, with the X-count only coming into play in the event of a tie such as a 100-7X vs a 100-5X. Several individual stages add up to a daily aggregate like the '595-30X' mentioned earlier. The high three daily aggs out of five possible events (competitors may attend any or all of these qualifiers) are added up, plus the daily score from the one required event, and forms a grand aggregate for league champion, etc. Don't know if that makes it any clearer or not ;) Its something that is relatively easy, but time consuming, to do by way of Mk1 Mod0 eyeball (the sorting/filtering) but a bit more challenging to make happen in Excel (so far). Thanks, Monte |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to sum 'n' largest values *not* in an array?
How do I sum the high 3 values from '585','592','595','599','0' when
they aren't in adjacent cells which I can give as an array value? Assuming that B2, D2, F2, H2, and J2 contain the five values, try... =SUM(LARGE((B2,D2,F2,H2,J2),{1,2,3})) -- Domenic http://www.xl-central.com In article , memilanuk wrote: Hello, I'm trying to find a way to take the high four scores from a league occurring over the spring/summer. Six total scores are possible, one event is mandatory, so I need the high three from the remaining five events. Clear as mud? ;) If the scores were simply one numeric value, I think I could make it work using 'sum' and 'large', judging by what I see in the help files. Therein lies the problem. The scores are recorded in a format similar to '595-30X', where the total points is '595', and '30X' is a tie-breaker value (i.e. if two people have the same number of points, but one has more 'X's, the higher X-count prevails). In order to make the formulas a little simpler, I've broken the scores apart with '595' in one column, and '30' in the one immediately adjacent to it. It looks close enough that a non-computer savvy person looking at it will recognize the scores for what they are, and makes adding up the points, adding up the X-count, and then concatenating the two back into a final cell in the format 'nnnn-xxx' relatively simple. Except... it makes it so the row for a given competitor looks like '585','20','592','20','595','30','599','38','0','0 ' (assuming competitor makes 4 out of the 5 qualifiers but misses the last one) How do I sum the high 3 values from '585','592','595','599','0' when they aren't in adjacent cells which I can give as an array value? Do I have to copy the values to some hidden cells or another sheet and do the array calcs on those (now adjacent) values? Is there a way to do this directly with out copying the values somewhere else? TIA, Monte |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to sum 'n' largest values *not* in an array?
On May 10, 2:14*pm, Domenic wrote:
How do I sum the high 3 values from '585','592','595','599','0' when they aren't in adjacent cells which I can give as an array value? Assuming that B2, D2, F2, H2, and J2 contain the five values, try... =SUM(LARGE((B2,D2,F2,H2,J2),{1,2,3})) Domenic, That worked pretty well; I had thought I had to enter the values for an array by highlighting and doing the Ctrl+Shift+Enter thing to get the curly braces ({ }) but not so this time. Slick! Now... for an another sticking point: now that I have the sum of the high 3 out of 5 scores in terms of points... what about the X-counts? I mean, I know now the aggregate point value, but how do I add the X- counts that go with those particular scores - which may or may not be the highest ones (its entirely possible to win by points but have a low X-count). An example might be something like 585-20X, 592-20X, 595-30X, 599-27X, 591-21X. Putting it in terms of what I'd do looking at it by 'eye', I'd find the top three scores (which we did using large () ), and then bring the corresponding X-count values along with the point values, add up all the points and all the Xs for each competitor and then rank each person by points, then by X count. After that, the tie-breaking rules get kind of obscure - goes back to who dropped a point last, which involves going through the score cards that were turned in, shot by shot. If it comes down to that (fairly rarely), I think we're going to be doing that by hand anyways. I'm thinking perhaps setting up some hidden cells - 6 columns, two (points & Xs) for each of their 'high 3', and fill them with the appropriate values using '=large((b4,d4,e4,f4,h4,i4)),1)' for the first point value, and then find someway to reference the cell immediately to the right of the cell that had the large value to copy the X count over. large() seems to pull the cell value, but not the cell address, so I'm not sure that will work. Any other ideas? TIA, Monte |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to sum 'n' largest values *not* in an array?
That's the same thought process I had which is why I asked about the
difference between a score and a "X" value. -- Biff Microsoft Excel MVP "Ragdyer" wrote in message ... If I understand you completely, I don't see your problem! Since your X scores will *never* be even close to being the largest value, just include those cells in the Large() function ... they'll *never* enter into the calculation. Say your example data was in D2 to M2. Try this: =SUM(LARGE(D2:M2,{1,2,3})) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "memilanuk" wrote in message ... On May 10, 10:46 am, "T. Valko" wrote: '585','20','592','20','595','30','599','38','0',' 0' What's the "rule" that differentiates the "X" values from the scores? Just looking at the values, the scores are significantly higher than the "X" values. If you can define a "rule" to separate the 2 then this can be done with a relatively simple formula, otherwise, it'll be slightly more complicated. For example, sum the highest 3 values that are greater than 50 (highest possible "X" value ???). The scores are from a bullseye type target with a decimal (10,9,8,7,6,5) scoring system. The highest possible value for any individual shot is '10'. Inside the 10-ring is another scoring ring, the 'X' ring. Any hit in the X ring still counts as 10 points, but also counts as a tie-breaker. So ten shots all in the 10 ring but with only seven inside the X ring would be noted as '100-7X'. A 100-1X beats a 99-9X, because the highest point value wins first, with the X-count only coming into play in the event of a tie such as a 100-7X vs a 100-5X. Several individual stages add up to a daily aggregate like the '595-30X' mentioned earlier. The high three daily aggs out of five possible events (competitors may attend any or all of these qualifiers) are added up, plus the daily score from the one required event, and forms a grand aggregate for league champion, etc. Don't know if that makes it any clearer or not ;) Its something that is relatively easy, but time consuming, to do by way of Mk1 Mod0 eyeball (the sorting/filtering) but a bit more challenging to make happen in Excel (so far). Thanks, Monte |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to sum 'n' largest values *not* in an array?
For the X-counts...
Assumptions: B2, D2, F2, H2, and J2 contain the point values C2, E2, G2, I2, and K2 contain the corresponding X-counts The sum of the X-counts does not exceed three digits in length Formula: =RIGHT(SUM(LARGE(IF(MOD(COLUMN(B2:J2)-COLUMN(B2),2)=0,B2:J2+C2:K2/1000),{ 1,2,3})),3)+0 ....confirmed with CONTROL+SHIFT+ENTER. -- Domenic http://www.xl-central.com In article , memilanuk wrote: On May 10, 2:14*pm, Domenic wrote: How do I sum the high 3 values from '585','592','595','599','0' when they aren't in adjacent cells which I can give as an array value? Assuming that B2, D2, F2, H2, and J2 contain the five values, try... =SUM(LARGE((B2,D2,F2,H2,J2),{1,2,3})) Domenic, That worked pretty well; I had thought I had to enter the values for an array by highlighting and doing the Ctrl+Shift+Enter thing to get the curly braces ({ }) but not so this time. Slick! Now... for an another sticking point: now that I have the sum of the high 3 out of 5 scores in terms of points... what about the X-counts? I mean, I know now the aggregate point value, but how do I add the X- counts that go with those particular scores - which may or may not be the highest ones (its entirely possible to win by points but have a low X-count). An example might be something like 585-20X, 592-20X, 595-30X, 599-27X, 591-21X. Putting it in terms of what I'd do looking at it by 'eye', I'd find the top three scores (which we did using large () ), and then bring the corresponding X-count values along with the point values, add up all the points and all the Xs for each competitor and then rank each person by points, then by X count. After that, the tie-breaking rules get kind of obscure - goes back to who dropped a point last, which involves going through the score cards that were turned in, shot by shot. If it comes down to that (fairly rarely), I think we're going to be doing that by hand anyways. I'm thinking perhaps setting up some hidden cells - 6 columns, two (points & Xs) for each of their 'high 3', and fill them with the appropriate values using '=large((b4,d4,e4,f4,h4,i4)),1)' for the first point value, and then find someway to reference the cell immediately to the right of the cell that had the large value to copy the X count over. large() seems to pull the cell value, but not the cell address, so I'm not sure that will work. Any other ideas? TIA, Monte |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Largest of Two Values | Excel Worksheet Functions | |||
Second largest in array of difference in high-low values in cells | Excel Worksheet Functions | |||
Reference to the N-largest value in an array | Excel Worksheet Functions | |||
find largest values, then return corresponding row values. | Excel Discussion (Misc queries) | |||
How to find the largest product of an array of values? | Excel Worksheet Functions |