ed.ayers315
Board Regular
- Joined
- Dec 14, 2009
- Messages
- 166
Here is a data set I am using. I need to show the percentage of variance, positive and negative. In the last column, you can see the result of this formula is wack. That column also has conditional formatting, DataBar. I need the databar to visually represent the actual percentage above or below the surv usage target. The formula in that column is <iferror(if(an11<0,am11 al11-1,(al11="" am11-1)),"")="">
</iferror(if(an11<0,am11>
<iferror(if(an11<0,am11 al11-1,(al11="" am11-1)),"")="">iferror(if(an11< 0,am11 al11-1,(al11="" am11-1)),"")=""
AN11 is Per Surv
AM11 is Periods current average
Any direction would be appreciated!
<tbody>
</tbody>
</iferror(if(an11<0,am11>
</iferror(if(an11<0,am11>
<iferror(if(an11<0,am11 al11-1,(al11="" am11-1)),"")="">iferror(if(an11< 0,am11 al11-1,(al11="" am11-1)),"")=""
AN11 is Per Surv
AM11 is Periods current average
Any direction would be appreciated!
PER SURV | PERIOD'S CURRENT AVG | AVG VARIANCE TO SURVEY | JAN: 2018 | FEB: 2018 | MAR: 2018 | APR: 2018 | MAY: 2018 | JUN: 2018 | JUL: 2017 | AUG: 2017 | SEP: 2017 | OCT: 2017 | NOV: 2017 | DEC: 2017 | EASY VIEW DATA BARS % VARIANCE | |
156.6 | 181.9 | 25.3 | 193.4 | 187.8 | 160.2 | 199.5 | 192.5 | 206.2 | 170.1 | 195.2 | 182.4 | 197.7 | 212.2 | 85.4 | 618.4% | |
30.0 | 38.5 | 8.5 | 42.5 | 41.4 | 27.0 | 23.2 | 1.8 | 41.9 | 42.6 | 43.1 | 35.2 | 53.9 | 52.1 | 57.2 | 353.7% | |
159.9 | 165.3 | 5.4 | 173.4 | 163.5 | 133.1 | 174.0 | 160.2 | 163.9 | 160.7 | 54.8 | 163.4 | 197.9 | 172.8 | 265.9 | 2934.3% | |
7.9 | 9.8 | 1.9 | 8.3 | 9.6 | 9.7 | 10.5 | 11.0 | 8.9 | 8.5 | 6.4 | 11.6 | 13.1 | 11.8 | 8.2 | 418.5% | |
0.5 | 0.5 | 0.0 | 1.2 | 0.1 | 0.1 | 1.4 | 0.4 | 0.4 | 0.1 | 0.1 | 0.9 | 0.7 | 0.2 | 0.5 | 1168.0% | |
2.4 | 2.5 | 0.0 | 0.3 | 2.7 | 2.9 | 0.7 | 3.0 | 2.1 | 3.4 | 2.8 | 2.3 | 2.6 | 1.9 | 5.2 | 6776.2% | |
5.5 | 2.5 | (2.9) | 0.0 | 0.9 | 4.3 | 5.4 | 10.6 | 9.1 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | -185.9% |
<tbody>
</tbody>
</iferror(if(an11<0,am11>
Last edited by a moderator: