Excel 2007 Pivot Table conditional formatting

[Gary Burke]

Hi all, I have a pivot table showing products in rows (about 4,000), Avg Cost in values, Location in columns.

I wish to use conditional formatting, unless there is a better way, to highlight those Avg Costs which are less than 95% of the Average for all locations, and those that are greater than 105% of the Average for all locations.

Example of data
Loc 1 Loc 2 Loc 3 Loc 4
Prod 1 $10.00 $15.00 $20.00
Prod 2 $15.00 $17.00 $25.00
Prod 3 $10.00 $11.00 $11.00 $11.00

EggHeadCafe - Software Developer Portal of Choice
Integrate Windows Live ID Authentication with ASP.NET Membership, Profiles and Roles
http://www.eggheadcafe.com/tutorial...9-fbcb8e17ef3a/integrate-windows-live-id.aspx

 

[Herbert Seidenberg]

Excel 2007 Pivot Table
If one standard deviation will do...
http://www.mediafire.com/file/iumtnmynbt3/10_21_09.xlsx

 

[Gary Burke]

Thanks Herbert for the response. It is greatly appreciated, however, 2 issues arise:-

1. It appears that I would need to create a conditional formatting rule for each row. There are 4,000+ row in the pivot table.
2. The users would prefer to use a % difference as opposed to a std deviation.



Herbert Seidenberg wrote:

Excel 2007 Pivot TableIf one standard deviation will do...http://www.mediafire.
21-Oct-09

Excel 2007 Pivot Tabl
If one standard deviation will do..
http://www.mediafire.com/file/iumtnmynbt3/10_21_09.xlsx

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
7Zip (LZMA) In-Memory Compression with C#
http://www.eggheadcafe.com/tutorial...9136-368603bcc27a/7zip-lzma-inmemory-com.aspx

 

[Herbert Seidenberg]

Excel 2007 Pivot Table
The problem is that PTs are dynamic and CFs are not.
CFs do not accept relative references.
Excel 2007 CFs offer only 2 choices in reference to above/below
average
1. 100%
2. x Stdev
For CFs to be dynamic, they have to follow changing PT rows,
not fixed rows/columns.
So the average is either fixed or is the average of the whole PT data
field.

Fortunately, CF dynamic trigger values (95%-105%) can be generated by
a macro:
(No worries about 4K rows, Excel 07 has no limit on the # of CFs)
http://www.mediafire.com/file/hxymndyantr/10_21_09.xlsm

 

[Herbert Seidenberg]

Excel 2007 Pivot Table
The problem is that PTs are dynamic and CFs are not.
CFs do not accept relative references.
Excel 2007 CFs offer only 2 choices in reference to above/below
average
1. 100%
2. x Stdev
For CFs to be dynamic, they have to follow changing PT rows,
not fixed rows/columns.
So the average is either fixed or is the average of the whole PT data
field.

Fortunately, CF dynamic trigger values (95%-105%) can be generated by
a macro:
(No worries about 4K rows, Excel 07 has no limit on the # of CFs)
http://www.mediafire.com/file/hxymndyantr/10_21_09.xlsm