Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: Leave Null Values as Null not Zero

  1. #1
    Join Date
    20th July 2004
    Posts
    38

    Leave Null Values as Null not Zero

    I have numerous linked spreadsheets. the master spreadsheet is a summary of eight other spreadsheets. So for example, cell A3 on the master might be cell A3 on spreadsheet 1, plus cell A3 on spreadsheet 2, plus cell A3 on spreadsheet 3 and so on. The problem is, if cell A3 on all the 8 spreadsheets is blank (because it concerns data to be entered in December), the master spreadsheet cell A3 value is given as "0". This messes up the linked graph, as it returns a figure of 0 for December, when in fact we haven't got to December. Is there a formula that could be used, e.g. "if the cell A3 in spreadsheet 1 and cell a3 in spreadsheet 2... is empty, then master cell a3 = empty, otherwise master cell a3 = (cell a3 in spreadsheet 1, plus cell a3 in spreadsheet 2 etc.) Or is there an easier way?

    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?

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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    2nd August 2004
    Location
    Ontario - Canada
    Posts
    1,452

    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,"")
    Barbara - aka The Cat Lady

    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

  3. #3
    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. #4
    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. #5
    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. #6
    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. #7
    Join Date
    2nd August 2004
    Location
    Ontario - Canada
    Posts
    1,452

    Re: Leave Null Values as Null not Zero

    Quote 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.
    Barbara - aka The Cat Lady

    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

  8. #8
    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. #9
    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. #10
    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


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Ado Sql Null Values
    By chucktx in forum Excel and/or SQL Help
    Replies: 4
    Last Post: May 16th, 2007, 13:54
  2. Database Query Parameter, cell value, null or not null
    By abqa in forum Excel and/or SQL Help
    Replies: 7
    Last Post: August 21st, 2006, 22:15
  3. Eliminating null values
    By hgus393 in forum EXCEL HELP
    Replies: 7
    Last Post: January 30th, 2006, 21:35
  4. Converting Null values
    By X33 in forum EXCEL HELP
    Replies: 3
    Last Post: November 8th, 2005, 04:13
  5. Adding Null Values
    By kmaac in forum Excel and/or Access Help
    Replies: 4
    Last Post: October 28th, 2005, 23:10

Bookmarks

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