SOLVED

Office 365 Excel - Bar graph and zero values

Copper Contributor

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

12 Replies

@Yukontornado69 

Hi Chris,

If you apply custom format like this

image.png

it shows zero in desirable format if the value is 0, and nothing if the cell is blank.

@Sergei Baklan 

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

@Yukontornado69 ,

 

Chris, each of us every day finds something new in Excel, it's infinite. That's why I like it.

Glad to help.

@Sergei Baklan 

 

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

@Yukontornado69 

 

Hi Chris - sure, please submit the sample, with real file it's always easier to find the reason for the issue.

@Sergei Baklan 

 

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

best response confirmed by Yukontornado69 (Copper Contributor)
Solution

@Yukontornado69 ,

 

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

image.png

and the same for the chart

image.png

@Sergei Baklan 

 

Sergei,

 

Ok, wow that worked perfectly!  I understand how the custom formatting change reads and works.  However, could you school me a little bit on how your new formula works in the cells G156:R156 please?  I am not sure what the "-1E-20" is doing or how it works that replaces the show blank "".  

Thank you in advance,

 

Chris

Hi Chris,

 

These cells are not blanks or numbers, values into them are returned by formula. Formula in your variant returns some positive number or text (actually space or empty string). Within the chart empty strings are interpreted as zeroes when we apply number formats. The idea for the workaround is to have numbers only. Instead of empty string formula returns quite small negative number (1e-20 in this case) which from visualization point of view is equivalent of nothing.

After that we apply custom number format which for negative numbers shows empty string, for positive ones applies desired number format. I assume here what you don't have real negative numbers in data set.

 

 

@Sergei Baklan 

 

Sergei,

That is a very interesting way to solve this problem!  Meaning that I would have NEVER gotten to that answer, lol.  I would have danced around a solution for a very long time.  I appreciate your skills and I hope that I can reach out to you in the future to steal more of your knowledge!

@Yukontornado69 , thank you. But that's not the universal solution, just workaround for this concrete case. For another data that could be something else.

1 best response

Accepted Solutions
best response confirmed by Yukontornado69 (Copper Contributor)
Solution

@Yukontornado69 ,

 

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

image.png

and the same for the chart

image.png

View solution in original post