Forum Discussion
Office 365 Excel - Bar graph and zero values
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
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.
Hi Sergei,
Well....upon further testing it did not fix my issue =( If I uploaded my sheet, would you be willing to take a look at it so you can see exactly what is happening?
Thank you in advance,
Chris
Hi Chris - sure, please submit the sample, with real file it's always easier to find the reason for the issue.
Awesome, thank you! See attached:
We are working with the "3rd Next Appt MASTER" tab. At the bottom of the sheet there is a chart and graph labeled as "Third Next Available Apointment (Days) FY 2019. The chart months in G156:R156 are currently using my old custom formatting of 0.00;-0.00;; because I tried your suggestion without success. These cells are populated from the above monthly charts. If the charts calculated monthly in column "I" or "S" is a number greater than 0.0, it populates the bottom chart and graph correctly: See any month that has a value greater than 0.0. However, Starting in Jan 19, Feb 19 and Mar 19 (S103, I127 and S127) the calculated amounts will be 0.0. Once you insert "0.0" into those cells, the below chart displays the value as it should but it does not graph it as 0.0 (see Mar 19 [S127] as an example). Basically I only want a value to show in the graph when there is a value of 0.0 or greater in the cells G156:R156. If the month is still empty, then no zero or value to show in the graph. I really appreciate you looking at this!
Chris