1. ## 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. ## 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. ## Re: Linking Question (Excel 2003 SP1)

Thank you Hans

4. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: Linking Question (Excel 2003 SP1)

Thank you Hans,

14. ## 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. ## 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 Last

#### Posting Permissions

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