Announcement

Collapse
No announcement yet.

Leave Null Values as Null not Zero

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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, 21:31.

  • #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,"")
    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.

    Comment


    • #3
      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.

      Comment


      • #4
        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'?

        Comment


        • #5
          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.

          Comment


          • #6
            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, 22:25.

            Comment


            • #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.
              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.

              Comment


              • #8
                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.

                Comment


                • #9
                  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

                  Comment


                  • #10
                    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?

                    Comment


                    • #11
                      Re: Leave Null Values as Null not Zero

                      Have a look at this thread and see if the suggestions here help.
                      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.

                      Comment


                      • #12
                        Re: Leave Null Values as Null not Zero

                        Thanks again Barbara. I now just need to amend all the spreadsheets this time around using the information you've given me and then in future it'll be automated, saving me days of work each month!

                        Comment


                        • #13
                          Re: Leave Null Values as Null not Zero

                          That's the idea. Glad it all helped bring things together for you.
                          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.

                          Comment

                          Working...
                          X