Forum Discussion

Yukontornado69's avatar
Yukontornado69
Copper Contributor
Apr 12, 2019
Solved

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

  • SergeiBaklan's avatar
    SergeiBaklan
    Apr 17, 2019

    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

    and the same for the chart

12 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor

    Yukontornado69 

    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.

    • Yukontornado69's avatar
      Yukontornado69
      Copper Contributor

      SergeiBaklan 

      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

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor

        Yukontornado69 ,

         

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

        Glad to help.

Resources