LARGE Function with subtotals
 

I have a list of the following accounts:

Account Amount
Red 1
Red 2
Subtotal Red 3
Blue 10
Subtotal Blue 10
Black 3
Black 1
Black 8
Subtotal Black 11
Yellow 15
Subtotal Yellow 15
Green 1
Green 1
Green Subtotal 2

I want to return the 3 highest *subtotal* values. If I just use LARGE
then I'd get 15, 15, 11.

Do I have to use an array?

Any thoughts?

JB


--
hindsight
------------------------------------------------------------------------
hindsight's Profile: http://www.excelforum.com/member.php...fo&userid=6360
View this thread: http://www.excelforum.com/showthread...hreadid=392396

you could use =LARGE(B1:B100,1)+LARGE(B1:B100,2)+LARGE(B1:B100,3 ) to get
the sum, or use each portion in it's own cell, ie
=LARGE(B1:B100,1)
=LARGE(B1:B100,2)
=LARGE(B1:B100,3)


hindsight Wrote:
I have a list of the following accounts:

Account Amount
Red 1
Red 2
Subtotal Red 3
Blue 10
Subtotal Blue 10
Black 3
Black 1
Black 8
Subtotal Black 11
Yellow 15
Subtotal Yellow 15
Green 1
Green 1
Green Subtotal 2

I want to return the 3 highest *subtotal* values. If I just use LARGE
then I'd get 15, 15, 11.

Do I have to use an array?

Any thoughts?

JB


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=392396

One non-array formulas play ..

Assuming the sample table is in A1:B15,

Put in D2:
=IF(ISNUMBER(SEARCH("Subtotal",A2)),B2-ROW()/10^10,"")
(Leave D1 empty)

Put in E2:
=IF(ISERROR(LARGE($D:$D,ROWS($A$1:A1))),"",INDEX(A :A,MATCH(LARGE($D:$D,ROWS(
$A$1:A1)),$D:$D,0)))

Copy E2 across to F2

Select D2:F2, fill down to D15

Cols E & F will return the full descending sort of
the rows with "Subtotal", viz.:

Subtotal Yellow 15
Subtotal Black 11
Subtotal Blue 10
Subtotal Red 3
Green Subtotal 2
(blank rows below)

And should there be any tied amounts in the "Subtotal" rows,
these will appear within cols E & F in the same relative order
that they are within cols A & B.
(there's an implicit tie-breaker built into the criteria formula in col D)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"hindsight" wrote
in message ...

I have a list of the following accounts:

Account Amount
Red 1
Red 2
Subtotal Red 3
Blue 10
Subtotal Blue 10
Black 3
Black 1
Black 8
Subtotal Black 11
Yellow 15
Subtotal Yellow 15
Green 1
Green 1
Green Subtotal 2

I want to return the 3 highest *subtotal* values. If I just use LARGE
then I'd get 15, 15, 11.

Do I have to use an array?

Any thoughts?

JB


--
hindsight
------------------------------------------------------------------------
hindsight's Profile:
http://www.excelforum.com/member.php...fo&userid=6360
View this thread: http://www.excelforum.com/showthread...hreadid=392396

If your subtotals are the result of using the SUBTOTAL function, such
as...

=SUBTOTAL(9,B2:B3)

...you can use the following formula...

D2, copied down:

=LARGE(IF(1-SUBTOTAL(3,OFFSET($B$2:$B$15,ROW($B$2:$B$15)-ROW($B$2),0,1))=1,$B$2:$B$15),ROWS($D$2:D2))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

hindsight Wrote:
I have a list of the following accounts:

Account Amount
Red 1
Red 2
Subtotal Red 3
Blue 10
Subtotal Blue 10
Black 3
Black 1
Black 8
Subtotal Black 11
Yellow 15
Subtotal Yellow 15
Green 1
Green 1
Green Subtotal 2

I want to return the 3 highest *subtotal* values. If I just use LARGE
then I'd get 15, 15, 11.

Do I have to use an array?

Any thoughts?

JB


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=392396