| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Showing Cummulative Top5 results with an array-formula...?
Hi There,
Below are array-formulas, so use Ctrl+Shift+Enter: I can show the Top5 of my range D5:D200 as follows: =LARGE($D$5:$D$200,ROW(INDIRECT("1:5"))) Suppose this results in a range with output: {1000,500,250,125,0} I can show the sum of the Top5 as follows: =SUM(LARGE($D$5:$D$18,ROW(INDIRECT("1:5")))) Results in {1875} What I would like to achieve is an array-formula that shows: SumTop5-values: Being: 1875 SumTop4-Values: Being: 1875 SumTop3-values: Being:1750 SumTop2-values: Being 1500 SumTop1-Value: Being 1000 Hope you cam help? Thanks in advance, jen |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Showing Cummulative Top5 results with an array-formula...?
=SUM(LARGE($D$5:$D$18,ROW(INDIRECT("1:"&A1))))
and put the Topn number in A1 -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jen" wrote in message ... Hi There, Below are array-formulas, so use Ctrl+Shift+Enter: I can show the Top5 of my range D5:D200 as follows: =LARGE($D$5:$D$200,ROW(INDIRECT("1:5"))) Suppose this results in a range with output: {1000,500,250,125,0} I can show the sum of the Top5 as follows: =SUM(LARGE($D$5:$D$18,ROW(INDIRECT("1:5")))) Results in {1875} What I would like to achieve is an array-formula that shows: SumTop5-values: Being: 1875 SumTop4-Values: Being: 1875 SumTop3-values: Being:1750 SumTop2-values: Being 1500 SumTop1-Value: Being 1000 Hope you cam help? Thanks in advance, jen |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Showing Cummulative Top5 results with an array-formula...?
You could also try ..
Source range assumed in D5:D200 Put this in say, E5, array-entered with CSE*: =SUM(LARGE($D$5:$D$200,ROW(INDIRECT("1:"&6-ROWS($1:1))))) Copy E5 down by 5 rows to E9 to return desired results, viz: SumTop5-values: Being: 1875 SumTop4-Values: Being: 1875 SumTop3-values: Being:1750 SumTop2-values: Being 1500 SumTop1-Value: Being 1000 *press CTRL+SHIFT+ENTER to confirm the formula Alternatively, select a 5 cell col range, eg select F5:F9, then place this into the formula bar and array-enter with CSE (multi-cell array-enter): =SUM(LARGE($D$5:$D$200,ROW(INDIRECT("1:"&6-ROW(INDIRECT("1:5")))))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jen" wrote: Hi There, Below are array-formulas, so use Ctrl+Shift+Enter: I can show the Top5 of my range D5:D200 as follows: =LARGE($D$5:$D$200,ROW(INDIRECT("1:5"))) Suppose this results in a range with output: {1000,500,250,125,0} I can show the sum of the Top5 as follows: =SUM(LARGE($D$5:$D$18,ROW(INDIRECT("1:5")))) Results in {1875} What I would like to achieve is an array-formula that shows: SumTop5-values: Being: 1875 SumTop4-Values: Being: 1875 SumTop3-values: Being:1750 SumTop2-values: Being 1500 SumTop1-Value: Being 1000 Hope you cam help? Thanks in advance, jen |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Showing Cummulative Top5 results with an array-formula...?
Hi Bob,
That means that if I want to see the "cummulative" TOP5 all at "once", I use 5 different arrayformulas (and the 5 helper cells in your case) ... it is flexible enough for my case. I am just exploring the power of array-formulas and was wondering whether it could be done with 1?! Jen "Bob Phillips" wrote in message ... =SUM(LARGE($D$5:$D$18,ROW(INDIRECT("1:"&A1)))) and put the Topn number in A1 -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jen" wrote in message ... Hi There, Below are array-formulas, so use Ctrl+Shift+Enter: I can show the Top5 of my range D5:D200 as follows: =LARGE($D$5:$D$200,ROW(INDIRECT("1:5"))) Suppose this results in a range with output: {1000,500,250,125,0} I can show the sum of the Top5 as follows: =SUM(LARGE($D$5:$D$18,ROW(INDIRECT("1:5")))) Results in {1875} What I would like to achieve is an array-formula that shows: SumTop5-values: Being: 1875 SumTop4-Values: Being: 1875 SumTop3-values: Being:1750 SumTop2-values: Being 1500 SumTop1-Value: Being 1000 Hope you cam help? Thanks in advance, jen |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Showing Cummulative Top5 results with an array-formula...?
Hi Max,
=SUM(LARGE($D$5:$D$200,ROW(INDIRECT("1:"&6-ROW(INDIRECT("1:5")))))) Works wonderfully! Can I please extend it one more level...? How would it look like if want to check on another condition: The Top5 accumulative values IF the person (in column B) is called "Smith" SORT OF: =SUM(IF(B5:B200="Smith",LARGE($D$5:$D$200,ROW(INDI RECT("1:"&6-ROW(INDIRECT("1:5"))))),0)) But this does not seem to do the trick. Jen "Max" wrote in message ... You could also try .. Source range assumed in D5:D200 Put this in say, E5, array-entered with CSE*: =SUM(LARGE($D$5:$D$200,ROW(INDIRECT("1:"&6-ROWS($1:1))))) Copy E5 down by 5 rows to E9 to return desired results, viz: SumTop5-values: Being: 1875 SumTop4-Values: Being: 1875 SumTop3-values: Being:1750 SumTop2-values: Being 1500 SumTop1-Value: Being 1000 *press CTRL+SHIFT+ENTER to confirm the formula Alternatively, select a 5 cell col range, eg select F5:F9, then place this into the formula bar and array-enter with CSE (multi-cell array-enter): =SUM(LARGE($D$5:$D$200,ROW(INDIRECT("1:"&6-ROW(INDIRECT("1:5")))))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jen" wrote: Hi There, Below are array-formulas, so use Ctrl+Shift+Enter: I can show the Top5 of my range D5:D200 as follows: =LARGE($D$5:$D$200,ROW(INDIRECT("1:5"))) Suppose this results in a range with output: {1000,500,250,125,0} I can show the sum of the Top5 as follows: =SUM(LARGE($D$5:$D$18,ROW(INDIRECT("1:5")))) Results in {1875} What I would like to achieve is an array-formula that shows: SumTop5-values: Being: 1875 SumTop4-Values: Being: 1875 SumTop3-values: Being:1750 SumTop2-values: Being 1500 SumTop1-Value: Being 1000 Hope you cam help? Thanks in advance, jen |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Showing Cummulative Top5 results with an array-formula...?
=SUM(LARGE(IF($B$5:$B$200="Smith",$D$5:$D$200),ROW (INDIRECT("1:"&6-ROWS($1:1)))))
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jen" wrote in message ... Hi Max, =SUM(LARGE($D$5:$D$200,ROW(INDIRECT("1:"&6-ROW(INDIRECT("1:5")))))) Works wonderfully! Can I please extend it one more level...? How would it look like if want to check on another condition: The Top5 accumulative values IF the person (in column B) is called "Smith" SORT OF: =SUM(IF(B5:B200="Smith",LARGE($D$5:$D$200,ROW(INDI RECT("1:"&6-ROW(INDIRECT("1:5"))))),0)) But this does not seem to do the trick. Jen "Max" wrote in message ... You could also try .. Source range assumed in D5:D200 Put this in say, E5, array-entered with CSE*: =SUM(LARGE($D$5:$D$200,ROW(INDIRECT("1:"&6-ROWS($1:1))))) Copy E5 down by 5 rows to E9 to return desired results, viz: SumTop5-values: Being: 1875 SumTop4-Values: Being: 1875 SumTop3-values: Being:1750 SumTop2-values: Being 1500 SumTop1-Value: Being 1000 *press CTRL+SHIFT+ENTER to confirm the formula Alternatively, select a 5 cell col range, eg select F5:F9, then place this into the formula bar and array-enter with CSE (multi-cell array-enter): =SUM(LARGE($D$5:$D$200,ROW(INDIRECT("1:"&6-ROW(INDIRECT("1:5")))))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jen" wrote: Hi There, Below are array-formulas, so use Ctrl+Shift+Enter: I can show the Top5 of my range D5:D200 as follows: =LARGE($D$5:$D$200,ROW(INDIRECT("1:5"))) Suppose this results in a range with output: {1000,500,250,125,0} I can show the sum of the Top5 as follows: =SUM(LARGE($D$5:$D$18,ROW(INDIRECT("1:5")))) Results in {1875} What I would like to achieve is an array-formula that shows: SumTop5-values: Being: 1875 SumTop4-Values: Being: 1875 SumTop3-values: Being:1750 SumTop2-values: Being 1500 SumTop1-Value: Being 1000 Hope you cam help? Thanks in advance, jen |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Showing Cummulative Top5 results with an array-formula...?
`Sorry, should have been
=SUM(LARGE(IF($B$5:$B$200="Smith",$D$5:$D$200),ROW (INDIRECT("1:"&6-ROW(INDIRECT("1:5")))))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Bob Phillips" wrote in message ... =SUM(LARGE(IF($B$5:$B$200="Smith",$D$5:$D$200),ROW (INDIRECT("1:"&6-ROWS($1:1))))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jen" wrote in message ... Hi Max, =SUM(LARGE($D$5:$D$200,ROW(INDIRECT("1:"&6-ROW(INDIRECT("1:5")))))) Works wonderfully! Can I please extend it one more level...? How would it look like if want to check on another condition: The Top5 accumulative values IF the person (in column B) is called "Smith" SORT OF: =SUM(IF(B5:B200="Smith",LARGE($D$5:$D$200,ROW(INDI RECT("1:"&6-ROW(INDIRECT("1:5"))))),0)) But this does not seem to do the trick. Jen "Max" wrote in message ... You could also try .. Source range assumed in D5:D200 Put this in say, E5, array-entered with CSE*: =SUM(LARGE($D$5:$D$200,ROW(INDIRECT("1:"&6-ROWS($1:1))))) Copy E5 down by 5 rows to E9 to return desired results, viz: SumTop5-values: Being: 1875 SumTop4-Values: Being: 1875 SumTop3-values: Being:1750 SumTop2-values: Being 1500 SumTop1-Value: Being 1000 *press CTRL+SHIFT+ENTER to confirm the formula Alternatively, select a 5 cell col range, eg select F5:F9, then place this into the formula bar and array-enter with CSE (multi-cell array-enter): =SUM(LARGE($D$5:$D$200,ROW(INDIRECT("1:"&6-ROW(INDIRECT("1:5")))))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jen" wrote: Hi There, Below are array-formulas, so use Ctrl+Shift+Enter: I can show the Top5 of my range D5:D200 as follows: =LARGE($D$5:$D$200,ROW(INDIRECT("1:5"))) Suppose this results in a range with output: {1000,500,250,125,0} I can show the sum of the Top5 as follows: =SUM(LARGE($D$5:$D$18,ROW(INDIRECT("1:5")))) Results in {1875} What I would like to achieve is an array-formula that shows: SumTop5-values: Being: 1875 SumTop4-Values: Being: 1875 SumTop3-values: Being:1750 SumTop2-values: Being 1500 SumTop1-Value: Being 1000 Hope you cam help? Thanks in advance, jen |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Showing Cummulative Top5 results with an array-formula...?
Hi Bob,
I could get this one to work ...with your help! :) =SUM(LARGE(IF($B$5:$B$200="Smith",$D$5:$D$200),ROW (INDIRECT("1:"&6-ROW(INDIRECT("1:5)))))) or =SUM(LARGE(IF($B$5:$B$200="Smith",$D$5:$D$200),ROW (INDIRECT("1:"&($J$23+1)-ROW(INDIRECT("1:"&$J$23)))))) With J23= 5 (4,3,2,1, ....whatever) But I do not manage to get your solution to work? ... =SUM(LARGE(IF($B$5:$B$18="Smith",$D$5:$D$18),ROW(I NDIRECT("1:"&6-ROWS($1:1))))) turns #NUM! I tried =SUM(LARGE(IF($B$5:$B$18="Smith",$D$5:$D$18),ROW(I NDIRECT("1:"&6-ROWS($5:5))))) with the same result And somehow I look like a Purbeck that this does not work ;) Jen "Bob Phillips" wrote in message ... =SUM(LARGE(IF($B$5:$B$200="Smith",$D$5:$D$200),ROW (INDIRECT("1:"&6-ROWS($1:1))))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jen" wrote in message ... Hi Max, =SUM(LARGE($D$5:$D$200,ROW(INDIRECT("1:"&6-ROW(INDIRECT("1:5")))))) Works wonderfully! Can I please extend it one more level...? How would it look like if want to check on another condition: The Top5 accumulative values IF the person (in column B) is called "Smith" SORT OF: =SUM(IF(B5:B200="Smith",LARGE($D$5:$D$200,ROW(INDI RECT("1:"&6-ROW(INDIRECT("1:5"))))),0)) But this does not seem to do the trick. Jen "Max" wrote in message ... You could also try .. Source range assumed in D5:D200 Put this in say, E5, array-entered with CSE*: =SUM(LARGE($D$5:$D$200,ROW(INDIRECT("1:"&6-ROWS($1:1))))) Copy E5 down by 5 rows to E9 to return desired results, viz: SumTop5-values: Being: 1875 SumTop4-Values: Being: 1875 SumTop3-values: Being:1750 SumTop2-values: Being 1500 SumTop1-Value: Being 1000 *press CTRL+SHIFT+ENTER to confirm the formula Alternatively, select a 5 cell col range, eg select F5:F9, then place this into the formula bar and array-enter with CSE (multi-cell array-enter): =SUM(LARGE($D$5:$D$200,ROW(INDIRECT("1:"&6-ROW(INDIRECT("1:5")))))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jen" wrote: Hi There, Below are array-formulas, so use Ctrl+Shift+Enter: I can show the Top5 of my range D5:D200 as follows: =LARGE($D$5:$D$200,ROW(INDIRECT("1:5"))) Suppose this results in a range with output: {1000,500,250,125,0} I can show the sum of the Top5 as follows: =SUM(LARGE($D$5:$D$18,ROW(INDIRECT("1:5")))) Results in {1875} What I would like to achieve is an array-formula that shows: SumTop5-values: Being: 1875 SumTop4-Values: Being: 1875 SumTop3-values: Being:1750 SumTop2-values: Being 1500 SumTop1-Value: Being 1000 Hope you cam help? Thanks in advance, jen |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Showing Cummulative Top5 results with an array-formula...?
Ah,
Sorry didn't see your correction ... Happy Purbeck! :) Thanks a million. Jen "Bob Phillips" wrote in message ... `Sorry, should have been =SUM(LARGE(IF($B$5:$B$200="Smith",$D$5:$D$200),ROW (INDIRECT("1:"&6-ROW(INDIRECT("1:5")))))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Bob Phillips" wrote in message ... =SUM(LARGE(IF($B$5:$B$200="Smith",$D$5:$D$200),ROW (INDIRECT("1:"&6-ROWS($1:1))))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jen" wrote in message ... Hi Max, =SUM(LARGE($D$5:$D$200,ROW(INDIRECT("1:"&6-ROW(INDIRECT("1:5")))))) Works wonderfully! Can I please extend it one more level...? How would it look like if want to check on another condition: The Top5 accumulative values IF the person (in column B) is called "Smith" SORT OF: =SUM(IF(B5:B200="Smith",LARGE($D$5:$D$200,ROW(INDI RECT("1:"&6-ROW(INDIRECT("1:5"))))),0)) But this does not seem to do the trick. Jen "Max" wrote in message ... You could also try .. Source range assumed in D5:D200 Put this in say, E5, array-entered with CSE*: =SUM(LARGE($D$5:$D$200,ROW(INDIRECT("1:"&6-ROWS($1:1))))) Copy E5 down by 5 rows to E9 to return desired results, viz: SumTop5-values: Being: 1875 SumTop4-Values: Being: 1875 SumTop3-values: Being:1750 SumTop2-values: Being 1500 SumTop1-Value: Being 1000 *press CTRL+SHIFT+ENTER to confirm the formula Alternatively, select a 5 cell col range, eg select F5:F9, then place this into the formula bar and array-enter with CSE (multi-cell array-enter): =SUM(LARGE($D$5:$D$200,ROW(INDIRECT("1:"&6-ROW(INDIRECT("1:5")))))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jen" wrote: Hi There, Below are array-formulas, so use Ctrl+Shift+Enter: I can show the Top5 of my range D5:D200 as follows: =LARGE($D$5:$D$200,ROW(INDIRECT("1:5"))) Suppose this results in a range with output: {1000,500,250,125,0} I can show the sum of the Top5 as follows: =SUM(LARGE($D$5:$D$18,ROW(INDIRECT("1:5")))) Results in {1875} What I would like to achieve is an array-formula that shows: SumTop5-values: Being: 1875 SumTop4-Values: Being: 1875 SumTop3-values: Being:1750 SumTop2-values: Being 1500 SumTop1-Value: Being 1000 Hope you cam help? Thanks in advance, jen |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Showing Cummulative Top5 results with an array-formula...?
Purbeck?
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jen" wrote in message ... Ah, Sorry didn't see your correction ... Happy Purbeck! :) Thanks a million. Jen "Bob Phillips" wrote in message ... `Sorry, should have been =SUM(LARGE(IF($B$5:$B$200="Smith",$D$5:$D$200),ROW (INDIRECT("1:"&6-ROW(INDIRECT("1:5")))))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Bob Phillips" wrote in message ... =SUM(LARGE(IF($B$5:$B$200="Smith",$D$5:$D$200),ROW (INDIRECT("1:"&6-ROWS($1:1))))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jen" wrote in message ... Hi Max, =SUM(LARGE($D$5:$D$200,ROW(INDIRECT("1:"&6-ROW(INDIRECT("1:5")))))) Works wonderfully! Can I please extend it one more level...? How would it look like if want to check on another condition: The Top5 accumulative values IF the person (in column B) is called "Smith" SORT OF: =SUM(IF(B5:B200="Smith",LARGE($D$5:$D$200,ROW(INDI RECT("1:"&6-ROW(INDIRECT("1:5"))))),0)) But this does not seem to do the trick. Jen "Max" wrote in message ... You could also try .. Source range assumed in D5:D200 Put this in say, E5, array-entered with CSE*: =SUM(LARGE($D$5:$D$200,ROW(INDIRECT("1:"&6-ROWS($1:1))))) Copy E5 down by 5 rows to E9 to return desired results, viz: SumTop5-values: Being: 1875 SumTop4-Values: Being: 1875 SumTop3-values: Being:1750 SumTop2-values: Being 1500 SumTop1-Value: Being 1000 *press CTRL+SHIFT+ENTER to confirm the formula Alternatively, select a 5 cell col range, eg select F5:F9, then place this into the formula bar and array-enter with CSE (multi-cell array-enter): =SUM(LARGE($D$5:$D$200,ROW(INDIRECT("1:"&6-ROW(INDIRECT("1:5")))))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jen" wrote: Hi There, Below are array-formulas, so use Ctrl+Shift+Enter: I can show the Top5 of my range D5:D200 as follows: =LARGE($D$5:$D$200,ROW(INDIRECT("1:5"))) Suppose this results in a range with output: {1000,500,250,125,0} I can show the sum of the Top5 as follows: =SUM(LARGE($D$5:$D$18,ROW(INDIRECT("1:5")))) Results in {1875} What I would like to achieve is an array-formula that shows: SumTop5-values: Being: 1875 SumTop4-Values: Being: 1875 SumTop3-values: Being:1750 SumTop2-values: Being 1500 SumTop1-Value: Being 1000 Hope you cam help? Thanks in advance, jen |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Showing Cummulative Top5 results with an array-formula...?
Purbeck?
Perhaps something to do with your previous sign-off, Bob? <g ... looking out across Poole Harbour to the Purbecks -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Showing Cummulative Top5 results with an array-formula...?
I have come across plenty of old postings from you, where you sign of with:
" ... looking out across Poole Harbour to the Purbecks " If you would be looking outside on Poole Harbour now ...you would see me very happy :) Jen "Bob Phillips" wrote in message ... Purbeck? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jen" wrote in message ... Ah, Sorry didn't see your correction ... Happy Purbeck! :) Thanks a million. Jen "Bob Phillips" wrote in message ... `Sorry, should have been =SUM(LARGE(IF($B$5:$B$200="Smith",$D$5:$D$200),ROW (INDIRECT("1:"&6-ROW(INDIRECT("1:5")))))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Bob Phillips" wrote in message ... =SUM(LARGE(IF($B$5:$B$200="Smith",$D$5:$D$200),ROW (INDIRECT("1:"&6-ROWS($1:1))))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jen" wrote in message ... Hi Max, =SUM(LARGE($D$5:$D$200,ROW(INDIRECT("1:"&6-ROW(INDIRECT("1:5")))))) Works wonderfully! Can I please extend it one more level...? How would it look like if want to check on another condition: The Top5 accumulative values IF the person (in column B) is called "Smith" SORT OF: =SUM(IF(B5:B200="Smith",LARGE($D$5:$D$200,ROW(INDI RECT("1:"&6-ROW(INDIRECT("1:5"))))),0)) But this does not seem to do the trick. Jen "Max" wrote in message ... You could also try .. Source range assumed in D5:D200 Put this in say, E5, array-entered with CSE*: =SUM(LARGE($D$5:$D$200,ROW(INDIRECT("1:"&6-ROWS($1:1))))) Copy E5 down by 5 rows to E9 to return desired results, viz: SumTop5-values: Being: 1875 SumTop4-Values: Being: 1875 SumTop3-values: Being:1750 SumTop2-values: Being 1500 SumTop1-Value: Being 1000 *press CTRL+SHIFT+ENTER to confirm the formula Alternatively, select a 5 cell col range, eg select F5:F9, then place this into the formula bar and array-enter with CSE (multi-cell array-enter): =SUM(LARGE($D$5:$D$200,ROW(INDIRECT("1:"&6-ROW(INDIRECT("1:5")))))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jen" wrote: Hi There, Below are array-formulas, so use Ctrl+Shift+Enter: I can show the Top5 of my range D5:D200 as follows: =LARGE($D$5:$D$200,ROW(INDIRECT("1:5"))) Suppose this results in a range with output: {1000,500,250,125,0} I can show the sum of the Top5 as follows: =SUM(LARGE($D$5:$D$18,ROW(INDIRECT("1:5")))) Results in {1875} What I would like to achieve is an array-formula that shows: SumTop5-values: Being: 1875 SumTop4-Values: Being: 1875 SumTop3-values: Being:1750 SumTop2-values: Being 1500 SumTop1-Value: Being 1000 Hope you cam help? Thanks in advance, jen |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Showing Cummulative Top5 results with an array-formula...?
I thought that Max, but he uses the word in two posts, and I can't figure it
? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Max" wrote in message ... Purbeck? Perhaps something to do with your previous sign-off, Bob? <g ... looking out across Poole Harbour to the Purbecks -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Showing Cummulative Top5 results with an array-formula...?
You sailing on the harbour then? It's nice and windy :-)
"Jen" wrote in message ... I have come across plenty of old postings from you, where you sign of with: " ... looking out across Poole Harbour to the Purbecks " If you would be looking outside on Poole Harbour now ...you would see me very happy :) Jen "Bob Phillips" wrote in message ... Purbeck? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jen" wrote in message ... Ah, Sorry didn't see your correction ... Happy Purbeck! :) Thanks a million. Jen "Bob Phillips" wrote in message ... `Sorry, should have been =SUM(LARGE(IF($B$5:$B$200="Smith",$D$5:$D$200),ROW (INDIRECT("1:"&6-ROW(INDIRECT("1:5")))))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Bob Phillips" wrote in message ... =SUM(LARGE(IF($B$5:$B$200="Smith",$D$5:$D$200),ROW (INDIRECT("1:"&6-ROWS($1:1))))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jen" wrote in message ... Hi Max, =SUM(LARGE($D$5:$D$200,ROW(INDIRECT("1:"&6-ROW(INDIRECT("1:5")))))) Works wonderfully! Can I please extend it one more level...? How would it look like if want to check on another condition: The Top5 accumulative values IF the person (in column B) is called "Smith" SORT OF: =SUM(IF(B5:B200="Smith",LARGE($D$5:$D$200,ROW(INDI RECT("1:"&6-ROW(INDIRECT("1:5"))))),0)) But this does not seem to do the trick. Jen "Max" wrote in message ... You could also try .. Source range assumed in D5:D200 Put this in say, E5, array-entered with CSE*: =SUM(LARGE($D$5:$D$200,ROW(INDIRECT("1:"&6-ROWS($1:1))))) Copy E5 down by 5 rows to E9 to return desired results, viz: SumTop5-values: Being: 1875 SumTop4-Values: Being: 1875 SumTop3-values: Being:1750 SumTop2-values: Being 1500 SumTop1-Value: Being 1000 *press CTRL+SHIFT+ENTER to confirm the formula Alternatively, select a 5 cell col range, eg select F5:F9, then place this into the formula bar and array-enter with CSE (multi-cell array-enter): =SUM(LARGE($D$5:$D$200,ROW(INDIRECT("1:"&6-ROW(INDIRECT("1:5")))))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jen" wrote: Hi There, Below are array-formulas, so use Ctrl+Shift+Enter: I can show the Top5 of my range D5:D200 as follows: =LARGE($D$5:$D$200,ROW(INDIRECT("1:5"))) Suppose this results in a range with output: {1000,500,250,125,0} I can show the sum of the Top5 as follows: =SUM(LARGE($D$5:$D$18,ROW(INDIRECT("1:5")))) Results in {1875} What I would like to achieve is an array-formula that shows: SumTop5-values: Being: 1875 SumTop4-Values: Being: 1875 SumTop3-values: Being:1750 SumTop2-values: Being 1500 SumTop1-Value: Being 1000 Hope you cam help? Thanks in advance, jen |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Showing Cummulative Top5 results with an array-formula...?
You will get a #NUM error if there aren't 5 entries for "Smith" (in column B)
or at least that's what I got when I tested your formula BUt this worked: =SUM(LARGE(IF($B$5:$B$18="Smith",$D$5:$D$18),ROW(I NDIRECT("1:"&(COUNTIF($B$5:$B$18,"Smith")+1)-ROWS($1:1))))) HTH "Jen" wrote: Hi Bob, I could get this one to work ...with your help! :) =SUM(LARGE(IF($B$5:$B$200="Smith",$D$5:$D$200),ROW (INDIRECT("1:"&6-ROW(INDIRECT("1:5)))))) or =SUM(LARGE(IF($B$5:$B$200="Smith",$D$5:$D$200),ROW (INDIRECT("1:"&($J$23+1)-ROW(INDIRECT("1:"&$J$23)))))) With J23= 5 (4,3,2,1, ....whatever) But I do not manage to get your solution to work? ... =SUM(LARGE(IF($B$5:$B$18="Smith",$D$5:$D$18),ROW(I NDIRECT("1:"&6-ROWS($1:1))))) turns #NUM! I tried =SUM(LARGE(IF($B$5:$B$18="Smith",$D$5:$D$18),ROW(I NDIRECT("1:"&6-ROWS($5:5))))) with the same result And somehow I look like a Purbeck that this does not work ;) Jen "Bob Phillips" wrote in message ... =SUM(LARGE(IF($B$5:$B$200="Smith",$D$5:$D$200),ROW (INDIRECT("1:"&6-ROWS($1:1))))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jen" wrote in message ... Hi Max, =SUM(LARGE($D$5:$D$200,ROW(INDIRECT("1:"&6-ROW(INDIRECT("1:5")))))) Works wonderfully! Can I please extend it one more level...? How would it look like if want to check on another condition: The Top5 accumulative values IF the person (in column B) is called "Smith" SORT OF: =SUM(IF(B5:B200="Smith",LARGE($D$5:$D$200,ROW(INDI RECT("1:"&6-ROW(INDIRECT("1:5"))))),0)) But this does not seem to do the trick. Jen "Max" wrote in message ... You could also try .. Source range assumed in D5:D200 Put this in say, E5, array-entered with CSE*: =SUM(LARGE($D$5:$D$200,ROW(INDIRECT("1:"&6-ROWS($1:1))))) Copy E5 down by 5 rows to E9 to return desired results, viz: SumTop5-values: Being: 1875 SumTop4-Values: Being: 1875 SumTop3-values: Being:1750 SumTop2-values: Being 1500 SumTop1-Value: Being 1000 *press CTRL+SHIFT+ENTER to confirm the formula Alternatively, select a 5 cell col range, eg select F5:F9, then place this into the formula bar and array-enter with CSE (multi-cell array-enter): =SUM(LARGE($D$5:$D$200,ROW(INDIRECT("1:"&6-ROW(INDIRECT("1:5")))))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jen" wrote: Hi There, Below are array-formulas, so use Ctrl+Shift+Enter: I can show the Top5 of my range D5:D200 as follows: =LARGE($D$5:$D$200,ROW(INDIRECT("1:5"))) Suppose this results in a range with output: {1000,500,250,125,0} I can show the sum of the Top5 as follows: =SUM(LARGE($D$5:$D$18,ROW(INDIRECT("1:5")))) Results in {1875} What I would like to achieve is an array-formula that shows: SumTop5-values: Being: 1875 SumTop4-Values: Being: 1875 SumTop3-values: Being:1750 SumTop2-values: Being 1500 SumTop1-Value: Being 1000 Hope you cam help? Thanks in advance, jen |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Showing Cummulative Top5 results with an array-formula...?
Aha, so my hunch that it was related to your ex-sign-off was good.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Showing Top 30 with array-formula? | Excel Worksheet Functions | |||
| Array Formula Duplicating Results | Excel Discussion (Misc queries) | |||
| array formula count results of two tests | Excel Worksheet Functions | |||
| showing multiple results to a formula | Excel Worksheet Functions | |||
| Array formula returning wrong results | Excel Discussion (Misc queries) | |||
![Jen[_4_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
