Showing Cummulative Top5 results with an array-formula...?

[Jen]

Hi There,

Below are array-formulas, so use Ctrl+Shift+Enter:

I can show the Top5 of my range D5200 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

 

[Bob Phillips]

=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)

 

[Guest]

You could also try ..

Source range assumed in D5200

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"))))))

---

 

[Jen]

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

 

[Jen]

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(INDIRECT("1:"&6-ROW(INDIRECT("1:5"))))),0))
But this does not seem to do the trick.

Jen

 

[Bob Phillips]

=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)

 

[Bob Phillips]

`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)

 

[Jen]

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(INDIRECT("1:"&6-ROWS($1:1)))))
turns #NUM!
I tried
=SUM(LARGE(IF($B$5:$B$18="Smith",$D$5:$D$18),ROW(INDIRECT("1:"&6-ROWS($5:5)))))
with the same result

And somehow I look like a Purbeck that this does not work
Jen

 

[Jen]

Ah,
Sorry didn't see your correction ...

Happy Purbeck!
Thanks a million.

Jen

 

[Bob Phillips]

Purbeck?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Max]

Purbeck?

Perhaps something to do with your previous sign-off, Bob? <g>

... looking out across Poole Harbour to the Purbecks


---

 

[Jen]

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]

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)

 

[Bob Phillips]

You sailing on the harbour then? It's nice and windy

 

[Guest]

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(INDIRECT("1:"&(COUNTIF($B$5:$B$18,"Smith")+1)-ROWS($1:1)))))

HTH

 

[Max]

Aha, so my hunch that it was related to your ex-sign-off was good.

---