| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivotting - Formula w/ Varying Column
I am using a Pivot Table and I have some formulas to rank the top numbers in
each column. My problem is - at times there will be 7 columns and other times 3 or 4, so the "Grand Totals" column moves. Here is my formula:RANK(J6,$J$6:$J$500,0)+COUNTIF(J6:$J$6,J6)-1 But the the reference to J may need to be changed. Basically: If J5 ="Grand Total" use the above formula, but if i5="Grand Total" then use RANK(i6,$i$6:$i$500,0)+COUNTIF(i6:$i$6,i6)-1 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Pivotting - Formula w/ Varying Column
This is a real "mouthful" but it works (without having to use volatile
functions!). Assume the Grand Total can be in either H5:J5 The formula is entered in cell L6. =RANK(INDEX(H$6:J$500,ROWS(L$6:L6),MATCH("Grand Total",H$5:J$5,0)),INDEX(H$6:J$500,,MATCH("Grand Total",H$5:J$5,0)))+COUNTIF(INDEX(H$6:J$500,ROW(L$ 6:L$6),MATCH("Grand Total",H$5:J$5,0)):INDEX(H$6:J$500,ROWS(L$6:L6),MA TCH("Grand Total",H$5:J$5,0)),INDEX(H$6:J$500,ROWS(L$6:L6),MA TCH("Grand Total",H$5:J$5,0)))-1 Copied down -- Biff Microsoft Excel MVP "Bigfoot17" wrote in message ... I am using a Pivot Table and I have some formulas to rank the top numbers in each column. My problem is - at times there will be 7 columns and other times 3 or 4, so the "Grand Totals" column moves. Here is my formula:RANK(J6,$J$6:$J$500,0)+COUNTIF(J6:$J$6,J6)-1 But the the reference to J may need to be changed. Basically: If J5 ="Grand Total" use the above formula, but if i5="Grand Total" then use RANK(i6,$i$6:$i$500,0)+COUNTIF(i6:$i$6,i6)-1 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Pivotting - Formula w/ Varying Column
Slight tweak...doesn't effect or change the result. Just makes more logical
sense! In the COUNTIF function: ....+COUNTIF(INDEX(H$6:J$500,ROW(L$6:L$6)... Change ROW(L$6:L$6) to ROW(L$1:L$1) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... This is a real "mouthful" but it works (without having to use volatile functions!). Assume the Grand Total can be in either H5:J5 The formula is entered in cell L6. =RANK(INDEX(H$6:J$500,ROWS(L$6:L6),MATCH("Grand Total",H$5:J$5,0)),INDEX(H$6:J$500,,MATCH("Grand Total",H$5:J$5,0)))+COUNTIF(INDEX(H$6:J$500,ROW(L$ 6:L$6),MATCH("Grand Total",H$5:J$5,0)):INDEX(H$6:J$500,ROWS(L$6:L6),MA TCH("Grand Total",H$5:J$5,0)),INDEX(H$6:J$500,ROWS(L$6:L6),MA TCH("Grand Total",H$5:J$5,0)))-1 Copied down -- Biff Microsoft Excel MVP "Bigfoot17" wrote in message ... I am using a Pivot Table and I have some formulas to rank the top numbers in each column. My problem is - at times there will be 7 columns and other times 3 or 4, so the "Grand Totals" column moves. Here is my formula:RANK(J6,$J$6:$J$500,0)+COUNTIF(J6:$J$6,J6)-1 But the the reference to J may need to be changed. Basically: If J5 ="Grand Total" use the above formula, but if i5="Grand Total" then use RANK(i6,$i$6:$i$500,0)+COUNTIF(i6:$i$6,i6)-1 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Pivotting - Formula w/ Varying Column
This is my final answer! <g
Change ROW(L$6:L$6) to ROW(L$1:L$1) That can be reduced to ROW(L$1) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Slight tweak...doesn't effect or change the result. Just makes more logical sense! In the COUNTIF function: ...+COUNTIF(INDEX(H$6:J$500,ROW(L$6:L$6)... Change ROW(L$6:L$6) to ROW(L$1:L$1) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... This is a real "mouthful" but it works (without having to use volatile functions!). Assume the Grand Total can be in either H5:J5 The formula is entered in cell L6. =RANK(INDEX(H$6:J$500,ROWS(L$6:L6),MATCH("Grand Total",H$5:J$5,0)),INDEX(H$6:J$500,,MATCH("Grand Total",H$5:J$5,0)))+COUNTIF(INDEX(H$6:J$500,ROW(L$ 6:L$6),MATCH("Grand Total",H$5:J$5,0)):INDEX(H$6:J$500,ROWS(L$6:L6),MA TCH("Grand Total",H$5:J$5,0)),INDEX(H$6:J$500,ROWS(L$6:L6),MA TCH("Grand Total",H$5:J$5,0)))-1 Copied down -- Biff Microsoft Excel MVP "Bigfoot17" wrote in message ... I am using a Pivot Table and I have some formulas to rank the top numbers in each column. My problem is - at times there will be 7 columns and other times 3 or 4, so the "Grand Totals" column moves. Here is my formula:RANK(J6,$J$6:$J$500,0)+COUNTIF(J6:$J$6,J6)-1 But the the reference to J may need to be changed. Basically: If J5 ="Grand Total" use the above formula, but if i5="Grand Total" then use RANK(i6,$i$6:$i$500,0)+COUNTIF(i6:$i$6,i6)-1 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Pivotting - Formula w/ Varying Column
You are truly an MVP with me too. This worked fine and I have been able to
make mods to my situation (change the range, search for a cell value instead of text). I appreciate your looking at the problem and the proposed solution. "T. Valko" wrote: This is a real "mouthful" but it works (without having to use volatile functions!). Assume the Grand Total can be in either H5:J5 The formula is entered in cell L6. =RANK(INDEX(H$6:J$500,ROWS(L$6:L6),MATCH("Grand Total",H$5:J$5,0)),INDEX(H$6:J$500,,MATCH("Grand Total",H$5:J$5,0)))+COUNTIF(INDEX(H$6:J$500,ROW(L$ 6:L$6),MATCH("Grand Total",H$5:J$5,0)):INDEX(H$6:J$500,ROWS(L$6:L6),MA TCH("Grand Total",H$5:J$5,0)),INDEX(H$6:J$500,ROWS(L$6:L6),MA TCH("Grand Total",H$5:J$5,0)))-1 Copied down -- Biff Microsoft Excel MVP "Bigfoot17" wrote in message ... I am using a Pivot Table and I have some formulas to rank the top numbers in each column. My problem is - at times there will be 7 columns and other times 3 or 4, so the "Grand Totals" column moves. Here is my formula:RANK(J6,$J$6:$J$500,0)+COUNTIF(J6:$J$6,J6)-1 But the the reference to J may need to be changed. Basically: If J5 ="Grand Total" use the above formula, but if i5="Grand Total" then use RANK(i6,$i$6:$i$500,0)+COUNTIF(i6:$i$6,i6)-1 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Pivotting - Formula w/ Varying Column
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Bigfoot17" wrote in message ... You are truly an MVP with me too. This worked fine and I have been able to make mods to my situation (change the range, search for a cell value instead of text). I appreciate your looking at the problem and the proposed solution. "T. Valko" wrote: This is a real "mouthful" but it works (without having to use volatile functions!). Assume the Grand Total can be in either H5:J5 The formula is entered in cell L6. =RANK(INDEX(H$6:J$500,ROWS(L$6:L6),MATCH("Grand Total",H$5:J$5,0)),INDEX(H$6:J$500,,MATCH("Grand Total",H$5:J$5,0)))+COUNTIF(INDEX(H$6:J$500,ROW(L$ 6:L$6),MATCH("Grand Total",H$5:J$5,0)):INDEX(H$6:J$500,ROWS(L$6:L6),MA TCH("Grand Total",H$5:J$5,0)),INDEX(H$6:J$500,ROWS(L$6:L6),MA TCH("Grand Total",H$5:J$5,0)))-1 Copied down -- Biff Microsoft Excel MVP "Bigfoot17" wrote in message ... I am using a Pivot Table and I have some formulas to rank the top numbers in each column. My problem is - at times there will be 7 columns and other times 3 or 4, so the "Grand Totals" column moves. Here is my formula:RANK(J6,$J$6:$J$500,0)+COUNTIF(J6:$J$6,J6)-1 But the the reference to J may need to be changed. Basically: If J5 ="Grand Total" use the above formula, but if i5="Grand Total" then use RANK(i6,$i$6:$i$500,0)+COUNTIF(i6:$i$6,i6)-1 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Pivotting - Formula w/ Varying Column
I apologize if this is a repeat, but IE schizzed out on me when I submitted
an earlier attempt to ask this and I have not seen the post appear. The formula 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 I get the first tied name twice. Any chance you could show me how to add the tiebreaker to the formula? I though 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) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Pivotting - Formula w/ Varying Column
The formula does not break ties
The formula I suggested *does* break ties. If you're error trapping for empty cells try this version: =IF(INDEX(H$6:J$20,ROWS(L$6:L6),MATCH("Grand Total",H$5:J$5,0))="","",RANK(INDEX(H$6:J$20,ROWS( L$6:L6),MATCH("Grand Total",H$5:J$5,0)),INDEX(H$6:J$20,,MATCH("Grand Total",H$5:J$5,0)))+COUNTIF(INDEX(H$6:J$20,1,MATCH ("Grand Total",H$5:J$5,0)):INDEX(H$6:J$20,ROWS(L$6:L6),MAT CH("Grand Total",H$5:J$5,0)),INDEX(H$6:J$20,ROWS(L$6:L6),MAT CH("Grand Total",H$5:J$5,0)))-1) -- Biff Microsoft Excel MVP "Bigfoot17" wrote in message ... I apologize if this is a repeat, but IE schizzed out on me when I submitted an earlier attempt to ask this and I have not seen the post appear. The formula 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 I get the first tied name twice. Any chance you could show me how to add the tiebreaker to the formula? I though 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? Transposing? Normal Forming? Crosstabbing? | Excel Discussion (Misc queries) | |||
| varying column width by row | Excel Discussion (Misc queries) | |||
| linking one column or varying range to another column using same f | Excel Worksheet Functions | |||
| Varying column widths | Excel Discussion (Misc queries) | |||
| Varying column width in a column chart | Charts and Charting in Excel | |||
Linear Mode
