Problem linking Tables with blank cells using Power Pivot

[Course]

Im using Power Pivot to link tables within my workbook.

The tables have blank cells.

My problem is if I input 0 into the blank cells that affects my results when I use the Average Function in the Pivot Table and if I use text to fill the blank cells such as na the Pivot Table does not allow me to group Values.

Is there any way around this problem.

Thanks

 

[Fazza]

Hello

As the tables have blank cells, I'd expect the Average would ignore them so there shouldn't be any problem. IIRC that is how Nulls are treated.

If not, try specifically excluding them with clause : WHERE fieldname Is Not Null

Though that may not do exactly what you want. I can't say without further understanding of the data inputs & required outputs.

If that doesn't work, please explain exactly what is being done including some sample input & outputs.

regards

 

[scottsen]

Ya, this should all be a "non-problem". Just leave the blank cells as blank, and AVERAGE() will ignore it (not treat it as zero).

 

[Course]

Thanks for your rpely,

the problem is then that I can average values but if i leave the cells in the source data blank I cant group them in the pivot table

 

[scottsen]

Not super sure I am getting what you are saying, but ... I suspect it is that, by default, if all measures in a row of a pivot table return blank, Excel will filter out the row.

You can change that. On the Pivot Table Options, on the Display tab, there is a "Show Items With No Data on Rows" that you can turn on.

 

[Course]

i created a pivot table from a Table. the table has some blank cells.

because of this i cant group some headings unless i return to the table and fill in the blank cells with zeros.

if i fill in the blank cells with zeros the average functions dont give accurate results in the pivot table.

its a catch 22 situation

 

[scottsen]

Can you give us a link to your workbook or some sample data, there is clearly something I am not getting here...