Conditional Format Chart

jckenney

New Member
Joined
Nov 16, 2013
Messages
7
Question to the experts.

I have created a bar chart and I'd like to have the color of the vertical bar change in color when the value 25K has been reached or exceeded.

Is this task even possible?


Cheers James
 
Last edited:

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,338
Office Version
2019, 2016, 2013
Platform
Windows
have a look at peltier charts, i can think if you know which bar it will always be you could drive it from VBA, but it maybe possible
 

jckenney

New Member
Joined
Nov 16, 2013
Messages
7
Actual HoursCritical Hours
Cell 1Cavity #1PM #12500025000
PM #21900025000
PM #41800025000
PM #52600025000
PM #62200025000

<tbody>
</tbody>

<colgroup><col><col><col><col span="2"></colgroup><tbody>
</tbody>
 

thisoldman

Well-known Member
Joined
Jan 5, 2014
Messages
1,073
The easiest method is to create two new columns containing simple formulas.
Excel 2012
CDEFG
1Actual HoursCritical HoursNot CriticalOver Critical
2PM #12500025000025000
3PM #21900025000190000
4PM #41800025000180000
5PM #52600025000026000
6PM #62200025000220000

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
F2=IF(D2 < E2, D2, 0)
G2=IF(D2 >= E2, D2, 0)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>


Select columns C, F, and G and insert a stacked bar chart.
 
Last edited:

jckenney

New Member
Joined
Nov 16, 2013
Messages
7
Jon, The above scheme works although it's not actually what I wanted the chart to look like.

I'd like a stacked chart to change at 25k and display red above that value. I'm at a loss
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,851
Office Version
365
Platform
Windows, MacOS
So then the formula in the first column is MIN(Value, 25000) and in the second column is MAX(0, Value-25000). Stack these and you're golden.
 

thisoldman

Well-known Member
Joined
Jan 5, 2014
Messages
1,073
Something like this?



<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;background-color: #FFF2CC;;"></td><td style=";">Actual Hours</td><td style=";">Critical Hours</td><td style="background-color: #FFF2CC;;">Not Critical</td><td style="background-color: #FFF2CC;;">Critical</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="background-color: #FFF2CC;;">PM #1</td><td style="text-align: right;;">25000</td><td style="text-align: right;;">25000</td><td style="text-align: right;background-color: #FFF2CC;;">24999</td><td style="text-align: right;background-color: #FFF2CC;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="background-color: #FFF2CC;;">PM #2</td><td style="text-align: right;;">19000</td><td style="text-align: right;;">25000</td><td style="text-align: right;background-color: #FFF2CC;;">19000</td><td style="text-align: right;background-color: #FFF2CC;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="background-color: #FFF2CC;;">PM #4</td><td style="text-align: right;;">18000</td><td style="text-align: right;;">25000</td><td style="text-align: right;background-color: #FFF2CC;;">18000</td><td style="text-align: right;background-color: #FFF2CC;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="background-color: #FFF2CC;;">PM #5</td><td style="text-align: right;;">26000</td><td style="text-align: right;;">25000</td><td style="text-align: right;background-color: #FFF2CC;;">24999</td><td style="text-align: right;background-color: #FFF2CC;;">1001</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F2</th><td style="text-align:left">=MIN(<font color="Blue">D2, E2-1</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G2</th><td style="text-align:left">=MAX(<font color="Blue">D2-F2, 0</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,094
Messages
5,466,609
Members
406,491
Latest member
amirkhosravi

This Week's Hot Topics

Top