Heat Map
in [General]
|
Prev: How do I remove a Calculated Field from the Pivot Table field list
Next: autocalculate doesn't work even with formula .
From: Desoto on 8 Apr 2010 23:07 I am trying to develop a "heat map" using Excel Conditional formating. I don't have a problem creating a basic conditional formatting table (heat map), however, I want to add two additional features. I want to merge cells so the the size of the merged cells is in relation to the signficance of the cost element or plant (more dollars = larger cell). I have not had a problem with that feature, however, in addition to the % positive or negative the cost element is to budget I also want to display a three letter acronym representing the plant. For example if the Atlanta plant was 3.4% favorable on labor cost the cell would be green and the text in cell would reflect ATL + 3.4%. I need the plant label so the reader will know what plant was favorable the 3.4%. I thought I could build the heat map using the conditional formatting and then build the label in the cells using concatenate or copy paste special from another worksheet but neither approach worked out. Any thoughts on how to design would be greatly appreciated!!
From: thexlguy on 10 Apr 2010 07:44
I'm having a little trouble figuring out what your spreadsheet looks like in my head :-), but I'll give it a go... I think a formula to create the label is the best approach. I envisioned a second table with the percentages loaded into the cells. Then, I would use a formula something like this: =[ATL_label_cell] & IF([percent_cell]>=0, " + ", " - ") & TEXT([percent_cell],"0.0%") where [ATL_label_cell] is the cell that contains your city labels and [percent_cell] is the cell that contains the percentage you want. So, if the ATL label was in B1, and the percentage for ATL was in F1: =B$1 & IF(F2>=0, " + ", " - ") & TEXT( F2,"0.0%") Hope this helps! Mike 'TheXLGuy.com Main Page' (http://thexlguy.com) -- thexlguy ------------------------------------------------------------------------ thexlguy's Profile: 1736 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=194224 http://www.thecodecage.com/forumz |