How to prevent Excel from creating a zero in a blank cell when referenced

Hi All,

I am having a data sheet which I use as a base for calculations. In a seperate sheet, I reference to this data sheet or use formulas to deduct ratios. I furthermore display graphs over a timeline. See example attached.
The Issue that I now have is that even though there data sheet has empty cells for the time after may 2016 the references and calculations still show it as 0 and it is therefore plotted on the chart. Is there an Easy way to get rid of this 0?

Since I do have several worksheets linked I'd rather not enter a formula in each cell as this would take me ages.

Thanks a lot for your help in advance!!


Capture.PNG

You can use an IF to test the cell for blank.

Instead of
=Sheet2!A1
Use
=IF(Sheet2!A1="","",Sheet2!A1)

Originally Posted by Jonmo1

You can use an IF to test the cell for blank.

Instead of
=Sheet2!A1
Use
=IF(Sheet2!A1="","",Sheet2!A1)

Hi Jonmo

I already reference the cells like this: =SUMIFS('Data Sheet'!$X:$X;'Data Sheet'!$BA:$BA;'YoY Content'!$A21;'Data Sheet'!$BB:$BB;'YoY Content'!H$18)
i.e. if i would have to change each cell individually i will spend days doing that.

also tried to adapt your approach to my formula (see below) but nothing changed. The output is still 0 instead of BLANK

=IF(SUMIFS('Data Sheet'!$X:$X;'Data Sheet'!$BA:$BA;'YoY Content'!$A21;'Data Sheet'!$BB:$BB;'YoY Content'!H$18)="";"";=SUMIFS('Data Sheet'!$X:$X;'Data Sheet'!$BA:$BA;'YoY Content'!$A21;'Data Sheet'!$BB:$BB;'YoY Content'!H$18))

The result of SUMIFS will NEVER be ""
It will always be a number (0 IS a number), so you'd have to do
=IF(SUMIFS('Data Sheet'!$X:$X;'Data Sheet'!$BA:$BA;'YoY Content'!$A21;'Data Sheet'!$BB:$BB;'YoY Content'!H$18)=0;"";SUMIFS('Data Sheet'!$X:$X;'Data Sheet'!$BA:$BA;'YoY Content'!$A21;'Data Sheet'!$BB:$BB;'YoY Content'!H$18))

But a shorter method (instead of doing sumifs twice) could be done like this
=IFERROR(1/(1/SUMIFS('Data Sheet'!$X:$X;'Data Sheet'!$BA:$BA;'YoY Content'!$A21;'Data Sheet'!$BB:$BB;'YoY Content'!H$18));"")

Originally Posted by Tony Valko

Try this...

=IFERROR(1/(1/SUMIFS('Data Sheet'!$X:$X;'Data Sheet'!$BA:$BA;'YoY Content'!$A21;'Data Sheet'!$BB:$BB;'YoY Content'!H$18));"")

Hi Jonmo1 & Tony Valko

This worked for the cell i.e. the cell is now blank, but unfortunately the chart still shows the line in the bottom instead of just stopping after the last filled cell:

Capture1.PNG


Any way I could change that too that if the cell is empty no graph is drawn and as soon as the cell has a value the graph incorporates it?

Why bother with the IFERROR, unless for some reason you prefer #N/A instead of #Div/0!

=1/(1/SUMIFS('Data Sheet'!$X:$X;'Data Sheet'!$BA:$BA;'YoY Content'!$A21;'Data Sheet'!$BB:$BB;'YoY Content'!H$18))

Originally Posted by Tony Valko

I can't see *.png images on this site.

Just taking a wild guess...

=IFERROR(1/(1/SUMIFS('Data Sheet'!$X:$X;'Data Sheet'!$BA:$BA;'YoY Content'!$A21;'Data Sheet'!$BB:$BB;'YoY Content'!H$18));NA())

Great! This works for the Chart issue, but just generated another one

Trying to explain in words in case you can't see the below png. I have three line charts for the development of post (in total, mobile, desktop). In the end I have a pie chart that takes the sum of all the tables and shows %.
If I use your formula from above, all the sums return #N/A as one or several of the fields which should be summed up are #N/A.

Any help how i can get around this one now

Capture3.PNG

It worked! Thanks Tony!!!

now, only if you have time it would be interesting to know why I did all of that in order to know it the next time.

Mainly i'm interested in the bold parts below:

IFERROR(1/(1/SUMIFS('Data Sheet'!$X:$X;'Data Sheet'!$BA:$BA;'YoY Content'!$A21;'Data Sheet'!$BB:$BB;'YoY Content'!H$18));NA())

=SUMIF(A1:A5,"< 1E100")

Cheers,

The 1/(1/ part is a neat little math trick.
A very handy trick for hiding 0's in a formula that returns numerical values.

Take ANY number (the result of your sumifs), say 100 for example

1/(1/100)

1/100 = 0.01
Now you have 1/0.01
1/0.01 = 100


Basically, that little calcluation will always return the same original number.
With the exception of 0
1/0 = #Div/0!
The ifferror traps that error.

Originally Posted by Jonmo1

The 1/(1/ part is a neat little math trick.
A very handy trick for hiding 0's in a formula that returns numerical values.

Take ANY number (the result of your sumifs), say 100 for example

1/(1/100)

1/100 = 0.01
Now you have 1/0.01
1/0.01 = 100


Basically, that little calcluation will always return the same original number.
With the exception of 0
1/0 = #Div/0!
The ifferror traps that error.

Thanks a lot Guys!

You're welcome.