Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Pivot table calculated field: multiply Sum of FieldA with Max ofFieldB
Hello usenetters,
My question concerns Excel 2003. A user asked me about a calculated field in a pivot table. Some of the fields are DT_NOW, GW_WCR, GW_LOB. The formula of the calculated field should depend on the date (DT_NOW): Up to 30/06/2009: Rotation = GW_WCR / GW_LOB * 91 Starting 01/07/2009: Rotation = GW_WCR / GW_LOB * 28 She added a field to the underlying data called ROT_FACTOR, filled with 91 until 30th june and filled with 28 starting 1st july. The current formula is =IF(ROT_FACTOR300000;GW_WCR/GW_LOB*91;GW_WCR/GW_LOB*28) The cutoff value 300000 is an arbitrary value based on the empirical observation that the sum of ROT_FACTOR was always larger before 01/07. In other words, each row in the pivot table sums 3297 rows of the underlying data. (row = date) This formula is of course not future proof. Imagine that some day the underlying data has more than 10715 to sum per date, then the sum of all 28's will also be above 300000 and the formula will be wrong. The following formula gives a completely wrong result: =GW_WCR/GW_LOB*ROT_FACTOR because ROT_FACTOR is not the value 91 or 28, but the SUM of those values. This conclusion is based on a reply by Debra Dalgleish on another question: (and of course my own observations) http://www.tech-archive.net/Archive/.../msg02597.html "You can use a calculated field to divide the Sum of one field by the Sum of another. Even if the field is summarized by Count in the data area, its Sum will be used in the calculated field, not the Count. So, if you're trying to divide sum of FieldA by Count of FieldB, you could do the calculation outside of the pivot table, referring to the values in the pivot table. Or, add a field to the source data that contains a 1 for each record. Then, use a calculated field to divide FieldA by the Sum of this new field." Debra, if you are reading this, I could *REALLY* use your advice. I prefer not to do the calculations outside the pivot table, because there is also a pivot chart, and I don't want to make it more difficult for my user than it already is. I already considered replacing the 91 and 28 with 0 and 1 in the underlying data. That way the formula would have to be: =IF(ROT_FACTOR=0;GW_WCR/GW_LOB*91;GW_WCR/GW_LOB*28) This leaves room for only one more future value of ROT_FACTOR: -1, so 3 possible values in total: -1, 0, 1. It is left as an excercise to the reader why these are the only 3 values that you can use if you want to SUM an unknown number of rows. :-) The ideal formula would be: =IF(DT_NOW<DATE(2009;7;1);GW_WCR/GW_LOB*91;GW_WCR/GW_LOB*28) but of course this won't work either because DT_NOW is also a sum of all dates. I could really use some good advice to solve this inside the pivot table. If all else fails, I will take the -1,0,1 approach. Kind regards, Amedee (Thank you for letting me change the magnetic patterns on your hard disk.) |
#2
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Pivot table calculated field: multiply Sum of FieldA with Max ofFieldB
Excel 2007 Pivot Table.
Calculated field, extended source data. http://www.mediafire.com/file/1qyewt...07_06_09b.xlsx Excel 2003 will not play this file, nor be able to use the new 2007 features, but you can probably glean the method from the pictures. |
#3
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Pivot table calculated field: multiply Sum of FieldA with Max of F
Hi,
We (at least, I) have asked Microsoft for access to the record level detail for calculation purposes in the piovt table. At the present time we don't have that capability, so you are stuck with the two solution approaches you have discussed. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Amedee Van Gasse" wrote: Hello usenetters, My question concerns Excel 2003. A user asked me about a calculated field in a pivot table. Some of the fields are DT_NOW, GW_WCR, GW_LOB. The formula of the calculated field should depend on the date (DT_NOW): Up to 30/06/2009: Rotation = GW_WCR / GW_LOB * 91 Starting 01/07/2009: Rotation = GW_WCR / GW_LOB * 28 She added a field to the underlying data called ROT_FACTOR, filled with 91 until 30th june and filled with 28 starting 1st july. The current formula is =IF(ROT_FACTOR300000;GW_WCR/GW_LOB*91;GW_WCR/GW_LOB*28) The cutoff value 300000 is an arbitrary value based on the empirical observation that the sum of ROT_FACTOR was always larger before 01/07. In other words, each row in the pivot table sums 3297 rows of the underlying data. (row = date) This formula is of course not future proof. Imagine that some day the underlying data has more than 10715 to sum per date, then the sum of all 28's will also be above 300000 and the formula will be wrong. The following formula gives a completely wrong result: =GW_WCR/GW_LOB*ROT_FACTOR because ROT_FACTOR is not the value 91 or 28, but the SUM of those values. This conclusion is based on a reply by Debra Dalgleish on another question: (and of course my own observations) http://www.tech-archive.net/Archive/.../msg02597.html "You can use a calculated field to divide the Sum of one field by the Sum of another. Even if the field is summarized by Count in the data area, its Sum will be used in the calculated field, not the Count. So, if you're trying to divide sum of FieldA by Count of FieldB, you could do the calculation outside of the pivot table, referring to the values in the pivot table. Or, add a field to the source data that contains a 1 for each record. Then, use a calculated field to divide FieldA by the Sum of this new field." Debra, if you are reading this, I could *REALLY* use your advice. I prefer not to do the calculations outside the pivot table, because there is also a pivot chart, and I don't want to make it more difficult for my user than it already is. I already considered replacing the 91 and 28 with 0 and 1 in the underlying data. That way the formula would have to be: =IF(ROT_FACTOR=0;GW_WCR/GW_LOB*91;GW_WCR/GW_LOB*28) This leaves room for only one more future value of ROT_FACTOR: -1, so 3 possible values in total: -1, 0, 1. It is left as an excercise to the reader why these are the only 3 values that you can use if you want to SUM an unknown number of rows. :-) The ideal formula would be: =IF(DT_NOW<DATE(2009;7;1);GW_WCR/GW_LOB*91;GW_WCR/GW_LOB*28) but of course this won't work either because DT_NOW is also a sum of all dates. I could really use some good advice to solve this inside the pivot table. If all else fails, I will take the -1,0,1 approach. Kind regards, Amedee (Thank you for letting me change the magnetic patterns on your hard disk.) |
#4
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Pivot table calculated field: multiply Sum of FieldA with Max ofFieldB
On 7 jul, 01:05, Herbert Seidenberg wrote:
Excel 2007 Pivot Table. Calculated field, extended source data.http://www.mediafire.com/file/1qyewt...07_06_09b.xlsx Excel 2003 will not play this file, nor be able to use the new 2007 features, but you can probably glean the method from the pictures. Interesting. (Un)fortunately we won't be upgrading to Office 2007 - company orders. One of the reasons is our aging hardware, higher hardware requirements for Office 2007, and the economical crisis. Upgrading to Office 2007 would mean replacing a few ten thousand computers. Not to mention the training costs because there are quite a few differences between Office 2003 and Office 2007. And of course every custom application that interfaces with Office would have to be re-evaluated. |
#5
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Pivot table calculated field: multiply Sum of FieldA with Max ofF
On 7 jul, 01:07, Shane Devenshire
wrote: Hi, We (at least, I) have asked Microsoft for access to the record level detail for calculation purposes in the piovt table. *At the present time we don't have that capability, so you are stuck with the two solution approaches you have discussed. Thank you for your reply. I have suggested the 0, 1 approach to the user and she agreed. -- If this helps, please click the Yes button. It helps, but I don't see a Yes button. Perhaps you are using another web interface for this Usenet group? I am using Google groups, but I would rather use a real Usenet client like Thunderbird or Pan or XanaNews. Cheers, Shane Devenshire Kind regards, Amedee Van Gasse |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculated field in a pivot table? | Excel Worksheet Functions | |||
Calculated Field and Calculated Item in Pivot Table | Excel Discussion (Misc queries) | |||
Calculated field in pivot table | Excel Discussion (Misc queries) | |||
pivot table formulas for calculated field or calculated item | Excel Discussion (Misc queries) | |||
calculated field in a pivot table | Excel Discussion (Misc queries) |