• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Make gaps in chart when values come from formulas

Xen

Member
Hello, I have a problem where my formula-generated data can't create gaps in chart.

Zeros, well, they makes your chart look like disaster
na() is a huge help in the begging and the end of chart, works as intended, however, na()'s in the middle of the chart does not create gaps, values before errors get connected instead, which is totally not the way I want it.

I want to put something in formula or turn on some option (haven't found anything helpful myself) that will make gaps in chart for me, like if cell was truly blank (I can't make blanks manually, data gets pulled from other sheets, users choice)

Is there a workaround for this?
 

Attachments

  • Formula chart gaps.xlsx
    14.1 KB · Views: 5
Oh, thanks! This should do the trick, nice workaround!

I'm still curious tho, why some of the basic and obvious features are still missing in excel, like gaps in charts or horizontal filtering.
 
Hi Zen..

Basic and obvious feature is already there.. delete the data from cell..
and more basic thing.. to display 0 in the graph.. not "GAP" :)
 
Chart is used to compare restaurant (which restaurant is users choice, that's why I can't create gaps manually) turnover between multiple years, column chart simply doesn't look as clear visually.

My aim is to make clean reports, that are easy to understand without additional explanations where to look and what things mean. Another thing I want do is automate everything as much as possible, so there is no/minimal manual work or room for error. Balancing between these two is sometimes hard.
 
Back
Top