Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivotting - T. Valko
I posted the below followup question a few days after the original question
had been answered by T. Valko, so I don't think it has been seen. I am deparate for a resolution so at some risk I am posting my followup question 'on its own.' [Original post - "Pivotting w/Varying Column"] The formula below does not break ties, I end up with 1,2,3,4,4,6,7,8,8 This presents a problem when I attempt to pull the names in the ranked order from the pivot table I get the first tied name twice. Any chance you could show me how to add the tiebreaker to the formula? I thought it was there but apparently not. Oh, BTW I needed to do some error trapping and have been using the formula as it appears below. =IF(ISNA(RANK(INDEX(B$6:L$500,ROWS(L$6:L6),MATCH(" Grand Total",B$5:L$5,0)),INDEX(B$6:L$500,,MATCH("Grand Total",B$5:L$5,0)))+COUNTIF(INDEX(B$6:L$500,ROW(L$ 6:L$6),MATCH("Grand Total",B$5:L$5,0)):INDEX(B$6:L$500,ROWS(L$6:L6),MA TCH("Grand Total",B$5:L$5,0)),INDEX(B$6:L$500,ROWS(L$6:L6),MA TCH("Grand Total",B$5:L$5,0)))-1),"",RANK(INDEX(B$6:L$500,ROWS(L$6:L6),MATCH("Gra nd Total",B$5:L$5,0)),INDEX(B$6:L$500,,MATCH("Grand Total",B$5:L$5,0)))+COUNTIF(INDEX(B$6:L$500,ROW(L$ 6:L$6),MATCH("Grand Total",B$5:L$5,0)):INDEX(B$6:L$500,ROWS(L$6:L6),MA TCH("Grand Total",B$5:L$5,0)),INDEX(B$6:L$500,ROWS(L$6:L6),MA TCH("Grand Total",B$5:L$5,0)))-1) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivotting - T. Valko
See my most recent reply at the original thread.
-- Biff Microsoft Excel MVP "Bigfoot17" wrote in message ... I posted the below followup question a few days after the original question had been answered by T. Valko, so I don't think it has been seen. I am deparate for a resolution so at some risk I am posting my followup question 'on its own.' [Original post - "Pivotting w/Varying Column"] The formula below does not break ties, I end up with 1,2,3,4,4,6,7,8,8 This presents a problem when I attempt to pull the names in the ranked order from the pivot table I get the first tied name twice. Any chance you could show me how to add the tiebreaker to the formula? I thought it was there but apparently not. Oh, BTW I needed to do some error trapping and have been using the formula as it appears below. =IF(ISNA(RANK(INDEX(B$6:L$500,ROWS(L$6:L6),MATCH(" Grand Total",B$5:L$5,0)),INDEX(B$6:L$500,,MATCH("Grand Total",B$5:L$5,0)))+COUNTIF(INDEX(B$6:L$500,ROW(L$ 6:L$6),MATCH("Grand Total",B$5:L$5,0)):INDEX(B$6:L$500,ROWS(L$6:L6),MA TCH("Grand Total",B$5:L$5,0)),INDEX(B$6:L$500,ROWS(L$6:L6),MA TCH("Grand Total",B$5:L$5,0)))-1),"",RANK(INDEX(B$6:L$500,ROWS(L$6:L6),MATCH("Gra nd Total",B$5:L$5,0)),INDEX(B$6:L$500,,MATCH("Grand Total",B$5:L$5,0)))+COUNTIF(INDEX(B$6:L$500,ROW(L$ 6:L$6),MATCH("Grand Total",B$5:L$5,0)):INDEX(B$6:L$500,ROWS(L$6:L6),MA TCH("Grand Total",B$5:L$5,0)),INDEX(B$6:L$500,ROWS(L$6:L6),MA TCH("Grand Total",B$5:L$5,0)))-1) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivotting - Formula w/ Varying Column | Excel Worksheet Functions | |||
Pivotting? Transposing? Normal Forming? Crosstabbing? | Excel Discussion (Misc queries) | |||
MR T VALKO ..PLZ HELP | Excel Discussion (Misc queries) | |||
TO mr t.valko... | Excel Discussion (Misc queries) | |||
mr t.valko ..help | Excel Discussion (Misc queries) |