Pivot Table Calculated Items - suppress blank lines

  • Thread starter Thread starter graemeW
  • Start date Start date
G

graemeW

Hi there,

I've just used calculated items for the first time to calculate a
variance between two specific ledger types (Ledger Type being one of my
column fields).

This works nicely, but it fills in a zero for all rows regardless of
whether they have any data or not. The net result is that rows that
were previously nicely supressed due to no data pop back up with a 0 in
the variance column.

Any ideas?
 
Hi Graeme

Not sure whether this will stop the suppressed rows appearing, but it
will suppress the zero's.
Wrap your item formula with an IF()
something like
=IF(AND(ISNUMBER(S/L),ISNUMBER(P/L)),S/L-P/L,"")
 
thanks Roger, nice try....but I guess that because its totalling its
expecting something numeric, and i just get #value where i previously
had 0.
 
Hi Graeme

I assumed that what we were talking about was numeric data.
How can you be calculating variance in a PT on text values?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel 2007 Pivot Table - Problem with custom field? 2
Pivot table calculated item problems 5
Calculated Item in Pivot table 1
Problem with Calculated item in Pivot Table 1
Excel 2002 Pivot Table calculated field percentage. 3
How do I suppress a zero value or #VALUE! for a Pivot calculated i 9
Zeros in calculated items in pivot tables 5
Pivot Table Calculations 1

Back
Top