Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Calculated Field / Item
I have a Pivot Table structured as follows using the first Auto Format option.
I need to customize the items that get displayed in the Units Row Field column and the Price Data Field Column. Specifically I need the "Units" Row Field to actually be blank vs. "(blank)" when it's blank or 0 or ideally dependent on another undisplayed Field's ("UnitType") value being "YN". I also need the "Price" Data Field to show "INCLUDED" when its value is 0. I tried creating a Calulated Field for Units with the formula "=IF(Units=0,"",Units)", but when I tried to add it as a Row Field, it says "The field you are moving cannot be placed in that PivotTable area". I tried defining a Calulated Field for Price with the formula "=IF(TotalPrice=0,"INCLUDED",TotalPrice )", but it shows "#VALUE!" vs. "INCLUDED". Thanks! Cat Sub Item Description Unit Name Units Price Category A-23456789012345678901234567890 Subcagegory A-567890012345678901234567890 Item 1 1234567890 12345 $123456.12 Item 2 1234567890 12345 $123456.12 Subcagegory B-567890012345678901234567890 Item 1 1234567890 12345 $123456.12 Item 2 1234567890 12345 $123456.12 Category B-23456789012345678901234567890 Subcagegory A-567890012345678901234567890 Item 1 1234567890 12345 $123456.12 Item 2 1234567890 12345 $123456.12 Subcagegory B-567890012345678901234567890 Item 1 1234567890 12345 $123456.12 Item 2 1234567890 12345 $123456.12 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Calculated Field / Item
'Anyone? Anyone?' If I should post to another group, please let me know.
Any help would be appreciated. FYI, the relevant parts of the Source Table is as follows: Category Subcategory ItemDescription UnitName UnitType Selected Units TotalPrice Category A Subcagegory A Item 1 SQ FT. NUM Y 100 1000.00 Category A Subcagegory A Item 2 SQ.FT. NUM Y 100 1000.00 Category A Subcagegory B Item 1 SQ.FT. NUM Y 100 1000.00 Category A Subcagegory B Item 2 SQ.FT. NUM Y 100 1000.00 Category B Subcagegory A Item 1 SQ.FT. NUM Y 100 1000.00 Category B Subcagegory A Item 2 SQ.FT. NUM Y 100 1000.00 Category B Subcagegory B Item 1 SQ.FT. NUM Y 100 1000.00 Category B Subcagegory B Item 2 SQ.FT. NUM Y 100 1000.00 "tc69" wrote: I have a Pivot Table structured as follows using the first Auto Format option. I need to customize the items that get displayed in the Units Row Field column and the Price Data Field Column. Specifically I need the "Units" Row Field to actually be blank vs. "(blank)" when it's blank or 0 or ideally dependent on another undisplayed Field's ("UnitType") value being "YN". I also need the "Price" Data Field to show "INCLUDED" when its value is 0. I tried creating a Calulated Field for Units with the formula "=IF(Units=0,"",Units)", but when I tried to add it as a Row Field, it says "The field you are moving cannot be placed in that PivotTable area". I tried defining a Calulated Field for Price with the formula "=IF(TotalPrice=0,"INCLUDED",TotalPrice )", but it shows "#VALUE!" vs. "INCLUDED". Thanks! Cat Sub Item Description UnitName Units TotalPrice Category A-23456789012345678901234567890 Subcagegory A-567890012345678901234567890 Item 1 1234567890 12345 $123456.12 Item 2 1234567890 12345 $123456.12 Subcagegory B-567890012345678901234567890 Item 1 1234567890 12345 $123456.12 Item 2 1234567890 12345 $123456.12 Category B-23456789012345678901234567890 Subcagegory A-567890012345678901234567890 Item 1 1234567890 12345 $123456.12 Item 2 1234567890 12345 $123456.12 Subcagegory B-567890012345678901234567890 Item 1 1234567890 12345 $123456.12 Item 2 1234567890 12345 $123456.12 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Calculated Field / Item
As for the "(blank)" value in the row area, I don't think you can change the value itself. But you can use conditional formatting to change the font to white so that you can't see it. I usually use a macro for this, but you can also do it manually. Also, maybe you could create a column in the source data table that uses a modified version of the formula you tried before ("=IF(Units=0," ",Units)") -- just assign a value " " (space) rather than the zero-length string. Then use this new column in the Row area rather than the original Units column. As for placing a value of "INCLUDED" in the data area, this can't be done because the data area is strictly for numbers, not text. Below are a few ideas for possible ways to get across the same information, depending on whether the number was calculated from real data or from an empty data set: 1) make a calculated field like you did, but rather than assigning a value of "INCLUDED", assign a unique and recognizable numerical value that will stand out and tell you the same thing; 2) use the "for empty cells, show" option in the Pivot Table Options dialog box for whenever there is no data behind the calculation; 3) in the pivot table wizard layout, put the Price field in the data area twice, once to sum and once to count. The count will tell you how many times the row item appeared in the calculation (a count of zero means an empty data set); 4) use conditional formatting (just like with the "(blank)" issue) to make zero values (and/or blank values) stand out in the data area. I hope this helps. -- kbenda |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table, IF function, calculated item versus calculated field | Excel Discussion (Misc queries) | |||
pivot table calculated field or item | Excel Discussion (Misc queries) | |||
Calculated Field and Calculated Item in Pivot Table | Excel Discussion (Misc queries) | |||
Pivot Table Formulas Calculated Item / Calculated Field | Excel Programming | |||
pivot table formulas for calculated field or calculated item | Excel Discussion (Misc queries) |