Pivot table with cell color information

[arkk]

Hi All,
I am looking for some help with pivot table and chart. I have a data sheet in which the values are compared and color coded. Attached is a sample with the macro. If the actual value (eg: val1) has an expected value (val1Exp -- Exp suffixed) it will try to compare and color code with green if exact match else with Red. There is a tolerance of 0.01% applied to values with "Exp" suffix and coded amber to indicate tol applied. The ExpE values are exact matches.
In order to quickly analyse how many passed , Failed , Total values can a pivot table with a chart be used with the color coded information?
Also can we slice the information for a particular test or particular validation with actual and expected values passed/ failed etc?

The data set used in this example is val1 to val5. This is dynamic. I am happy to get the range from the macro and write to any cell if that helps.

Any help appreciated.

Many Thanks

 

[Chihiro]

You can't use interior color or other format to summarize data directly in Pivot.

However, you can use the condition logic applied to summarize.
Ex: Use calculated field, formula "=ABS(val1-valExp)/val1" will show variance. Set field number format to percentage.

How do you want it summarized? Do you want to show % variance when it is > 0.01%. Or just flag when pass/fail?

 

[arkk]

Thanks Chihiro for your response. Basically I wanted to get the quick analysis in form of charts/data like eg:
1. Number of tests run (10 in this case)
2. Validation for each case ( 5)
3. Total validations (50)
4. Total Number of validations passed( exact +tolerance)
a. Number of validations passed with Tolerance(9)
b. Number of validations passed with no Tol
5. Total Number of validations failed (7)
6. Tests that has all the validations failed
7. Validation which has failed in all tests
8. Sliced data for Test 1 with Val1 vs Val1Exp etc(color coding or Tol variation shown might help here )
9. Sliced data by EmpRef with chosen fileds etc etc

Hope you got an idea. My thinking is if cell formatting is not supported for pivot table then I may need to handle it in the same macro code to get this apart from the sliced stuff... I am sure there may be other better ways to do this. Help appreciated. Many Thanks

 

[Chihiro]

See attached Demo.

Using formulas and Pivot with calculated fields.



Edit: You should remove "Grand Total". Since all fields are calculated field, and using IF statements, it will throw off "Grand Total".

 

[arkk]

Thanks Chihiro. Seems I am getting closer . How did you get the Val1Var etc in the Pivot table fields?

 

[Chihiro]

PivotTable tools -> Analyze -> Fields, Items, & Sets.

 

[arkk]

Thanks this helps. Let me digest this. The next thing will be to get the sliced information of actual vs expected with the values(as in the data). Many Thanks again