Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Leave Null Values as Null not Zero

1. Member
Join Date
20th July 2004
Posts
38

## Leave Null Values as Null not Zero

On the same lines, when I link a cell directly to a cell in another spreadsheet, and the source cell is empty, again it returns a value of "0" in the linked cell, rather than leaving it blank. Is there a way to link the two so that the linked cell on the graph is null if the source is null?

Pete
Last edited by petehenson; October 24th, 2005 at 21:31.

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: Leave Null Values as Null not Zero

Without addressing your exact formula, something like: =IF(ISBLANK(A2),"",A2/100) but sometimes the ISBLANK doesn't evaluate properly because A2 looks blanks but contains a formula so then =IF(A2="","",A2/100) or =IF)A2<>"",A2/100,"")

3. Senior Member
Join Date
12th January 2005
Location
Dusseldorf
Posts
232

## Re: Leave Null Values as Null not Zero

Try this to return nothing if the sum of cells A1 on sheets 1 & 2 is 0 or sum them if it is not.
=IF(Sheet2!A1+Sheet1!A1 = 0,"",Sheet2!A1+Sheet1!A1)

You could try using a pivot table and pivot chart as these enable you to hide value such as blanks or months that you dont want to report on yet.

Excel Video Tutorials / Excel Dashboards Reports

4. Member
Join Date
20th July 2004
Posts
38

## Re: Leave Null Values as Null not Zero

Thanks for that. The one thing I'm struggling with is calculating if multiple cells are blank, e.g. =IF(A2<>"" and B2<>"" and C2 <>"",A2/100,""). This formula does not work. What must I use instead of 'and'?

Excel Video Tutorials / Excel Dashboards Reports

5. Senior Member
Join Date
12th January 2005
Location
Dusseldorf
Posts
232

## Re: Leave Null Values as Null not Zero

You can nest if's but only 7 at a time. Does my solution not work? Should do, although its not dependent on the ISBLANK function it is just saying if the sum of this + this+ this = nothing then display nothing otherwise do the sum.

Excel Video Tutorials / Excel Dashboards Reports

6. Member
Join Date
20th July 2004
Posts
38

## Re: Leave Null Values as Null not Zero

The complication is that some values may well be zero in the data, not null. But your formula would show them as null on the master when they should be zero. Put crudely, you may be recorded as having done no work in July, Auugust and September, and recorded as nothing in October, November and December because those months are not complete. I would need the total for July-September to return as "0", but the total for October-December to return as blank. Otherwise on my graph it will show that you have done no work in Oct-Dec, when in fact the graph line should stop at September.

Also, I can't hide the future months on the graph, as the graph is used to project future performance up to the end of the financial year.
Last edited by petehenson; October 24th, 2005 at 22:25.

Excel Video Tutorials / Excel Dashboards Reports

7. ## Re: Leave Null Values as Null not Zero

Originally Posted by petehenson
Thanks for that. The one thing I'm struggling with is calculating if multiple cells are blank, e.g. =IF(A2<>"" and B2<>"" and C2 <>"",A2/100,""). This formula does not work. What must I use instead of 'and'?
=IF(COUNTA(A2:C2)<>0,A2/100,"") or =IF(COUNTA(A1:B1,D1))<>0,A2/100,"") is one way but isn't always reliable is one of those cells has a formula that evaluate to "" and the othe 2 are blank.

8. Senior Member
Join Date
12th January 2005
Location
Dusseldorf
Posts
232

## Re: Leave Null Values as Null not Zero

why dont you try using a pivot chart? Its easy to then not report what you dont want to. so you could de select months oct-dec.

Excel Video Tutorials / Excel Dashboards Reports

9. Member
Join Date
20th July 2004
Posts
38

## Re: Leave Null Values as Null not Zero

Thanks both of you. Barbarr, I should have said that A2, B2 and C2 are on different spreadsheets which makes it more difficult. I think I should be able to use nested If's though as you suggest, Neil.

The reason I'm trying to do this is that I have 56 spreadsheets of 8 pages each to update each month, which get their data from a single master spreadsheet (provided by someone else, so I'm limited a bit in what I can do).

At the moment it's a case of adjusting every worksheet each month so the most recent month's data is showing. I'm trying to make this easier, hence my question. I think it'll take a lot of work out of it if the nested if's idea works, certainly for the graphs.

Thanks again,
Pete

Excel Video Tutorials / Excel Dashboards Reports

10. Member
Join Date
20th July 2004
Posts
38

## Re: Leave Null Values as Null not Zero

I sorted that now and it works fine on the spreadsheets. However when doing it on a graph - If(A1="","",A1) it plots A1 as 0 on the graph even if A1 is blank. There are two lines on the graph. The other line is the target for the whoel year, sp the graph must have all the months on display.

Is there any way to display just part of the other line, i.e. not the months that are returned as blank by the above formula?

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno