Forum Discussion
Office 365 Excel - Bar graph and zero values
I have data being pulled from other cells via formulas.... ex. =IF(ISBLANK(S103)," ",(S103)) This populates a cell in a chart that is used in a graph. It is currently formatted as "Custom: 0.0;-0.0;;" so that if the formula returns Blank that nothing is showing in the bar graph (specifically if it is blank I DO NOT want a zero to show in the graph). However.....If the formula pulls a value of 0.0 from a cell I DO want it to show 0.0 in the chart and also then graph the 0.0. I am struggling making both of these work at the same time. I have tried =IF(ISBLANK(I127)," ",IF(I127=0.0,0.0,(I127))) and also =IF(ISBLANK(S127)," ",IF(S127=0,"0.0",(S127))) without success. I think it is just the correct custom formatting that will solve this? Help!!??
Thank you for your time,
Chris
Yes, chart converts empty string ("") to zero. As workaround, in G156:R156 you may use formula like
=IF(ISBLANK(I7),-1E-20,I7)
instead of
=IF(ISBLANK(I7)," ",I7)
and apply to these cells custom format
[<0]"";[>=0]0.0
I assume all your numbers are zero or positive ones.
That returns such cells formating
and the same for the chart
12 Replies
Hi Chris,
If you apply custom format like this
it shows zero in desirable format if the value is 0, and nothing if the cell is blank.
Sergei you are the man! I have been pulling my hair out with the custom formatting for this LOL. I was close but kept dancing around the correct answer! Again, thank you so much for your response. I am sure you read this and laughed at my struggle!
Have an amazing day,
Chris Ames
Chris, each of us every day finds something new in Excel, it's infinite. That's why I like it.
Glad to help.
Anyone??
