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

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)

Originally Posted by hindsight

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

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 <at>yahoo<dot>com
----
"hindsight" <hindsight.1t64af_1123038308.9564@excelforum-nospam.com> wrote
in message news:hindsight.1t64af_1123038308.9564@excelforum-nospam.com...
>
> 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!

Originally Posted by hindsight

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