Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Lounger
    Join Date
    Dec 2005
    Posts
    37
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Linking Question (Excel 2003 SP1)

    While linking to cells from another worksheet, is there a way to prevent a linked cell from populating with a zero when the information in the original cell has no data (null) while, at the same time, a linked cell will populate with a zero when the original cell has a zero? I am using a median calculation on the linked information so the linked cell has to coincide with the original cell. Thank you.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linking Question (Excel 2003 SP1)

    You could use formulas like this one:
    <code>
    =IF(ISBLANK(Sheet3!A1),"",Sheet3!A1)
    </code>
    Functions such as SUM, AVERAGE and MEDIAN ignore text values, so they should return the desired value.

  3. #3
    Lounger
    Join Date
    Dec 2005
    Posts
    37
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Re: Linking Question (Excel 2003 SP1)

    Thank you Hans

  4. #4
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking Question (Excel 2003 SP1)

    An alternative would be to enter an apostrophe in the blank linked cell which forces excel to see null text.

  5. #5
    Lounger
    Join Date
    Dec 2005
    Posts
    37
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Re: Linking Question (Excel 2003 SP1)

    Is there a way of insuring that a blank cell stays blank when analyzing a range of data that includes the blank cell in a pivot table? This particular range of data is calculated from another column on the same worksheet with the following formula: =IF(L676=0,"",(C676/L676)*22.

    Thank you.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linking Question (Excel 2003 SP1)

    I'm sorry, I don't understand your question. Could you explain in more detail what you mean, and perhaps attach a sample workbook illustrating the problem?

  7. #7
    Lounger
    Join Date
    Dec 2005
    Posts
    37
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Re: Linking Question (Excel 2003 SP1)

    I apologize Hans,

    I will attach a workbook.

    I am trying to analyze data in a pivot table that will be eventually used for a median calculation. However, the pivot table is replacing blank cells from the referenced data (Sheet 1 Column D) with zeroes. The referenced data (in column D of Sheet 1) is derived by using a calculation involving columns C and L of Sheet 1 by the use of the formula: =IF(L676=0,"",(C676/L676)*22. Is there a method that will prevent the pivot table from populating zeroes into what should be blank cells? Can the double quotes ("") be replaced with something else in the formula to prevent this from happening?

    It does not seem that I can send this response with the attachment. Is there a way around this?

    Thank you.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linking Question (Excel 2003 SP1)

    The maximum size of an attachment is 100 KB. If your workbook is over that size, create a zip file from it and attach that.

  9. #9
    Lounger
    Join Date
    Dec 2005
    Posts
    37
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Re: Linking Question (Excel 2003 SP1)

    Thank you Hans,

    The workbook is attached.

    I am trying to analyze data in a pivot table that will be eventually used for a median calculation. However, the pivot table is replacing blank cells from the referenced data (Sheet 1 Column D) with zeroes. The referenced data (in column D of Sheet 1) is derived by using a calculation involving columns C and L of Sheet 1 by the use of the formula: =IF(L676=0,"",(C676/L676)*22. Is there a method that will prevent the pivot table from populating zeroes into what should be blank cells? Can the double quotes ("") be replaced with something else in the formula to prevent this from happening?


    Thank you.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linking Question (Excel 2003 SP1)

    The SUM function in Excel never returns a blank, even if all its arguments are blank - it returns 0 instead. You cannot avoid that.
    So this is not a problem of the pivot table, it is a consequence of the way Excel calculates sums.

    If you want to calculate the median of a column in the pivot table excluding 0 values, you can use an array formula (confirm with Ctrl+Shift+Enter) with MEDIAN and IF. See the attached version.
    It would be similar if you want to calculate the median of a row.

  11. #11
    Lounger
    Join Date
    Dec 2005
    Posts
    37
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Re: Linking Question (Excel 2003 SP1)

    Thank you Hans,

    Is there another function besides SUM that will return the actual value to the pivot table and yet still maintain a blank cell? The default seems to be COUNT which, in this case, returns a 1 in every cell.

    Thank you

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linking Question (Excel 2003 SP1)

    No, all these aggregate functions return a number, never a blank, and that's as it should be, even if you don't want it that way. The sum or count of something isn't blank, it's a number that can be 0 if there is nothing to sum or count.

  13. #13
    Lounger
    Join Date
    Dec 2005
    Posts
    37
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Re: Linking Question (Excel 2003 SP1)

    Thank you Hans,

  14. #14
    Lounger
    Join Date
    Dec 2005
    Posts
    37
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Re: Linking Question (Excel 2003 SP1)

    Can you think of another way to derive this four hour block pattern without the use of a pivot table? In particular, I am looking for a quick way to auto fill values, from a column of data, (Column D in this case), for a specific four hour block from each day of the month. The four values of each day would be distributed in cells beside each other in one of the 28 to 31 rows like they were in the pivot table.

    Thank you.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linking Question (Excel 2003 SP1)

    You could use INDEX and MATCH in array formulas, e.g. in B5:

    =INDEX('Sheet 1'!$D$3:$D$674,MATCH($A5&B$4,'Sheet 1'!$A$3:$A$674&'Sheet 1'!$B$3:$B$674,0))

    Because these are lookup formulas, not sums, they simply copy the blanks. See the attached version.

Page 1 of 2 12 LastLast

Posting Permissions

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