Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
Date & Time | New Users to Excel | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions | |||
Outlining function without subtotals | Excel Discussion (Misc queries) |