Help me with a 100% complete formula

halesowenmum

Active Member
Joined
Oct 20, 2010
Messages
380
Office Version
365
Platform
Windows
Hi. I have a stacked column chart and am setting up formulas to enable CF colouration of the chart columns.

E F G H I
0 26 51 76 100
25 50 75 99 100

Beside the first row which will contribute to the chart (row 9, first cell is C9 where the user enters the % progress they believe they've achieved) I have this formula which populates the % progress figure into the relevant column, e, f, g, h or I. [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=IF(AND(E$2<$C9,$C9<=E$3),$C9,"")[/FONT]

I've copied right and it works fab for all the columns but Col I where if I put 100 in, it doesn't return a number to the I column; it does return '100' but into Col E..

Can anyone help with how I need to adjust the formula to enable it to say '100' if the person enters 100??? Obviously it should only show if they put 100 so it's an = situation for the value of 100.

Thank you.
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

jorismoerings

Well-known Member
Joined
Jul 4, 2014
Messages
1,263
Hi,

The reason for not showing if the person enters a 100 is the logic in the formula. It says =IF(AND(E$2<$C9,$C9<=E$3),$C9,"").
Basically saying AND(100<100,100<=100) which will never result in TRUE hence no number will come up.

These are 2 possible solutions:
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">E</td><td style=";">F</td><td style=";">G</td><td style=";">H</td><td style=";">I</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">26</td><td style="text-align: right;;">51</td><td style="text-align: right;;">76</td><td style="text-align: right;;">100</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">25</td><td style="text-align: right;;">50</td><td style="text-align: right;;">75</td><td style="text-align: right;;">99</td><td style="text-align: right;;">100</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">23</td><td style="text-align: right;;"></td><td style="text-align: right;;">23</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">27</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">27</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">52</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">52</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">77</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">77</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">100</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">100</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: right;;">23</td><td style="text-align: right;;"></td><td style="text-align: right;;">23</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style="text-align: right;;">27</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;">27</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style="text-align: right;;">52</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">52</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style="text-align: right;;">77</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">77</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style="text-align: right;;">100</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">100</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 rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">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: rgb(255,255,255)" ><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: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><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: rgb(218,231,245);color: rgb(22,17,32)">E18</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">$C9>E$2,$C9<=E$3</font>),$C9,""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F18</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">$C9>E$3,$C9<=F$3</font>),$C9,""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E9</th><td style="text-align:left">=INDEX(<font color="Blue">FREQUENCY(<font color="Red">$C9,$E$3:$I$3</font>),COLUMN(<font color="Red"></font>)-4</font>)*$C9</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,102,291
Messages
5,485,972
Members
407,523
Latest member
Talicius

This Week's Hot Topics

Top