• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Will I be able to use formula based Conditional Formatting in my heat map?

A

AitchK

Member
I've got a conditionally formatted pivot table with 3 columns:
starttime (datetime) grouped into Hours
starrtime2 (datetime) grouped into Days of Month
Running, a column containing either 0 or 1 (off and on) depending on whether to denote if a machine was running. Aggregated with SUM, the pivot table shows how many machines were running at any given hour any time of the day.
Conditionally formatted using 3 color default settings, shows the lowest value in green and the highest value as red. But, my boss wants to use the heatmap as a dashboard, and to start turning red only when we're at 80% capacity of available machines.
While the data sheet behind the pivot table contains a column called SumAvailableMachines, corresponding to the other values, it isn't actually used for the heatmap. Since the number of available machines fluctuates from 879 to 1247, 80% is a moving target as well. So, I need CF to be flexible and show red only if the SUM(Running) value in the pivot table for that hour of day is 80% of the SumOfMachinesAvail.
I did try some formulas but CF isn't doing anything in response to them. eg. SUM(.80*H:H) where H is the SumAvailableMachines.
I'm wondering if I post my excel spreadsheet would someone be willing to take a look and offer advice on how to do this?
 
I've uploaded the file with just 10 hours worth of data.
Currently the 3 color CF is based on numbers 0, 500, and 1000. For this reason green predominates. But, I need the mid and high point to be formula based...
 

Attachments

  • HeatMapwConditionalFormatting.xlsx
    783.1 KB · Views: 9
Hi AitchK,

I dont think you can apply a 3 color scheme to get the results. The good news is, there is a workaround to it.

Here is what I did. I added a sheet and made a similar pivot to what you had but for "available" data. And used thresholds to color them - <0.4 Green , >0.8 Red and the rest as amber. You can create more thresholds to get a host of color combinations at various usage level. Then add CFs the way I have done in the below file.

Cheers,
BD
 

Attachments

  • HeatMapwConditionalFormatting.xlsx
    620.2 KB · Views: 10
BBD, that solves the problem and I will use this. I really appreciate your time and tutorial, as it puts an end to so many hours trying to find solution. Key learning points for me here are AND syntax, use of 3 separate formatting rules, and that I can apply rule to just one cell as long as I select 'All cells showing...." radio dial. Oh yeah, and the clever use of second pivot table. Great. Your taking time to read my problem, use my own sample, and repost it with solution is really tremendous. Thanks!
 
Last edited:
You must log in or register to reply here.
Back
Top