Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
djl djl is offline
external usenet poster
 
Posts: 8
Default How do I suppress a zero value or #VALUE! for a Pivot calculated i

I have entered the following formula within a new calculated item to
calculate the variance between actual and budget values with the view to
making zero values or error values a blank cell. When I insert the formula
it removes the logic of only displaying the records relevant to the page
setting and I get the same records repeated for each page setting with #VALUE!

Variance
=IF(OR(ISERROR(SUM(Budget)-SUM(Actual)),SUM(Budget)-SUM(Actual)=0),"",SUM(Budget)-SUM(Actual))

Thanks
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 18
Default How do I suppress a zero value or #VALUE! for a Pivot calculated i

Hai,
Try this I didn't try it. Just given like that
=IF(OR(ISERROR(SUM(Budget)-SUM(Actual)),(SUM(Budget)-SUM(Actual)=0)),"",SUM(Budget)-SUM(Actual))
--
Regards


"DJL" wrote:

I have entered the following formula within a new calculated item to
calculate the variance between actual and budget values with the view to
making zero values or error values a blank cell. When I insert the formula
it removes the logic of only displaying the records relevant to the page
setting and I get the same records repeated for each page setting with #VALUE!

Variance
=IF(OR(ISERROR(SUM(Budget)-SUM(Actual)),SUM(Budget)-SUM(Actual)=0),"",SUM(Budget)-SUM(Actual))

Thanks

  #3   Report Post  
Posted to microsoft.public.excel.misc
djl djl is offline
external usenet poster
 
Posts: 8
Default How do I suppress a zero value or #VALUE! for a Pivot calculat

No, it doesn't work - it is still showing #VALUE! for the non relevant records

"Vital_ar" wrote:

Hai,
Try this I didn't try it. Just given like that
=IF(OR(ISERROR(SUM(Budget)-SUM(Actual)),(SUM(Budget)-SUM(Actual)=0)),"",SUM(Budget)-SUM(Actual))
--
Regards


"DJL" wrote:

I have entered the following formula within a new calculated item to
calculate the variance between actual and budget values with the view to
making zero values or error values a blank cell. When I insert the formula
it removes the logic of only displaying the records relevant to the page
setting and I get the same records repeated for each page setting with #VALUE!

Variance
=IF(OR(ISERROR(SUM(Budget)-SUM(Actual)),SUM(Budget)-SUM(Actual)=0),"",SUM(Budget)-SUM(Actual))

Thanks

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 26
Default How do I suppress a zero value or #VALUE! for a Pivot calculated i

Use 0 instead of "" in your forlula.

Use IFERROR instead of ISERROR.

Use IFERROR at the beginning of formula not inbetween the formula.

Try below formula;

=IFERROR(SUM(Budget)-SUM(Actual),0)

Regards,
Pritesh


"DJL" wrote:

I have entered the following formula within a new calculated item to
calculate the variance between actual and budget values with the view to
making zero values or error values a blank cell. When I insert the formula
it removes the logic of only displaying the records relevant to the page
setting and I get the same records repeated for each page setting with #VALUE!

Variance
=IF(OR(ISERROR(SUM(Budget)-SUM(Actual)),SUM(Budget)-SUM(Actual)=0),"",SUM(Budget)-SUM(Actual))

Thanks

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,276
Default How do I suppress a zero value or #VALUE! for a Pivot calculat

Hi,

=IF(OR(ISERROR(SUM(Budget)-SUM(Actual)),(SUM(Budget)-SUM(Actual))=0),"",SUM(Budget)-SUM(Actual))

"DJL" wrote:

No, it doesn't work - it is still showing #VALUE! for the non relevant records

"Vital_ar" wrote:

Hai,
Try this I didn't try it. Just given like that
=IF(OR(ISERROR(SUM(Budget)-SUM(Actual)),(SUM(Budget)-SUM(Actual)=0)),"",SUM(Budget)-SUM(Actual))
--
Regards


"DJL" wrote:

I have entered the following formula within a new calculated item to
calculate the variance between actual and budget values with the view to
making zero values or error values a blank cell. When I insert the formula
it removes the logic of only displaying the records relevant to the page
setting and I get the same records repeated for each page setting with #VALUE!

Variance
=IF(OR(ISERROR(SUM(Budget)-SUM(Actual)),SUM(Budget)-SUM(Actual)=0),"",SUM(Budget)-SUM(Actual))

Thanks



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 376
Default How do I suppress a zero value or #VALUE! for a Pivot calculated i

Hi

Right click on the PTTable Optionscheck Show Error values as, and either
leave as blank or set to 0 as you wish.

--
-------
Regards
Roger Govier

"DJL" wrote in message
...
I have entered the following formula within a new calculated item to
calculate the variance between actual and budget values with the view to
making zero values or error values a blank cell. When I insert the
formula
it removes the logic of only displaying the records relevant to the page
setting and I get the same records repeated for each page setting with
#VALUE!

Variance
=IF(OR(ISERROR(SUM(Budget)-SUM(Actual)),SUM(Budget)-SUM(Actual)=0),"",SUM(Budget)-SUM(Actual))

Thanks

__________ Information from ESET Smart Security, version of virus
signature database 4897 (20100226) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 4897 (20100226) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 4901 (20100227) __________

The message was checked by ESET Smart Security.

http://www.eset.com



  #7   Report Post  
Posted to microsoft.public.excel.misc
djl djl is offline
external usenet poster
 
Posts: 8
Default How do I suppress a zero value or #VALUE! for a Pivot calculat

I am still getting the #VALUE! cells. I am wanting the Pivot Table to
exclude these records depending on the page setting as not all row fields
apply to every page setting

"Eduardo" wrote:

Hi,

=IF(OR(ISERROR(SUM(Budget)-SUM(Actual)),(SUM(Budget)-SUM(Actual))=0),"",SUM(Budget)-SUM(Actual))

"DJL" wrote:

No, it doesn't work - it is still showing #VALUE! for the non relevant records

"Vital_ar" wrote:

Hai,
Try this I didn't try it. Just given like that
=IF(OR(ISERROR(SUM(Budget)-SUM(Actual)),(SUM(Budget)-SUM(Actual)=0)),"",SUM(Budget)-SUM(Actual))
--
Regards


"DJL" wrote:

I have entered the following formula within a new calculated item to
calculate the variance between actual and budget values with the view to
making zero values or error values a blank cell. When I insert the formula
it removes the logic of only displaying the records relevant to the page
setting and I get the same records repeated for each page setting with #VALUE!

Variance
=IF(OR(ISERROR(SUM(Budget)-SUM(Actual)),SUM(Budget)-SUM(Actual)=0),"",SUM(Budget)-SUM(Actual))

Thanks

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,389
Default How do I suppress a zero value or #VALUE! for a Pivot calculated i

Unfortunately IFERROR works only in XL2007. The OP probably has XL2003 or
earlier.

Your suggestion of changing "" to 0 is good, though.

Regards,
Fred

"Pritesh" wrote in message
...
Use 0 instead of "" in your forlula.

Use IFERROR instead of ISERROR.

Use IFERROR at the beginning of formula not inbetween the formula.

Try below formula;

=IFERROR(SUM(Budget)-SUM(Actual),0)

Regards,
Pritesh


"DJL" wrote:

I have entered the following formula within a new calculated item to
calculate the variance between actual and budget values with the view to
making zero values or error values a blank cell. When I insert the
formula
it removes the logic of only displaying the records relevant to the page
setting and I get the same records repeated for each page setting with
#VALUE!

Variance
=IF(OR(ISERROR(SUM(Budget)-SUM(Actual)),SUM(Budget)-SUM(Actual)=0),"",SUM(Budget)-SUM(Actual))

Thanks


  #9   Report Post  
Posted to microsoft.public.excel.misc
djl djl is offline
external usenet poster
 
Posts: 8
Default How do I suppress a zero value or #VALUE! for a Pivot calculat

Thanks, that removes the #VALUE!s but it does not suppress the records that
have no data. It shows ALL the rows on each page but I only want the rows
showing that has data for that page setting. I have checked that all fields
do not have the "show items with no data" selected.


"Roger Govier" wrote:

Hi

Right click on the PTTable Optionscheck Show Error values as, and either
leave as blank or set to 0 as you wish.

--
-------
Regards
Roger Govier

"DJL" wrote in message
...
I have entered the following formula within a new calculated item to
calculate the variance between actual and budget values with the view to
making zero values or error values a blank cell. When I insert the
formula
it removes the logic of only displaying the records relevant to the page
setting and I get the same records repeated for each page setting with
#VALUE!

Variance
=IF(OR(ISERROR(SUM(Budget)-SUM(Actual)),SUM(Budget)-SUM(Actual)=0),"",SUM(Budget)-SUM(Actual))

Thanks

__________ Information from ESET Smart Security, version of virus
signature database 4897 (20100226) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 4897 (20100226) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 4901 (20100227) __________

The message was checked by ESET Smart Security.

http://www.eset.com



  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 376
Default How do I suppress a zero value or #VALUE! for a Pivot calculat

Hi

If you want to send me the file, I will take a look
roger at technology4u dot co dot uk
Change the at and dots to make valid email address
--
Regards
Roger Govier

DJL wrote:
Thanks, that removes the #VALUE!s but it does not suppress the records that
have no data. It shows ALL the rows on each page but I only want the rows
showing that has data for that page setting. I have checked that all fields
do not have the "show items with no data" selected.


"Roger Govier" wrote:

Hi

Right click on the PTTable Optionscheck Show Error values as, and either
leave as blank or set to 0 as you wish.

--
-------
Regards
Roger Govier

"DJL" wrote in message
...
I have entered the following formula within a new calculated item to
calculate the variance between actual and budget values with the view to
making zero values or error values a blank cell. When I insert the
formula
it removes the logic of only displaying the records relevant to the page
setting and I get the same records repeated for each page setting with
#VALUE!

Variance
=IF(OR(ISERROR(SUM(Budget)-SUM(Actual)),SUM(Budget)-SUM(Actual)=0),"",SUM(Budget)-SUM(Actual))

Thanks

__________ Information from ESET Smart Security, version of virus
signature database 4897 (20100226) __________

The message was checked by ESET Smart Security.

http://www.eset.com



__________ Information from ESET Smart Security, version of virus signature database 4897 (20100226) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 4901 (20100227) __________

The message was checked by ESET Smart Security.

http://www.eset.com





  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default How do I suppress a zero value or #VALUE! for a Pivot calculated i

On Friday, February 26, 2010 at 3:56:24 AM UTC-8, Roger Govier wrote:
Hi

Right click on the PTTable Optionscheck Show Error values as, and either
leave as blank or set to 0 as you wish.

--
-------
Regards
Roger Govier


Thank you SO MUCH for this!
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivottable calculated item - how to suppress zero? HMallen Excel Worksheet Functions 4 November 12th 08 05:40 PM
Suppress Zeros in a Pivot Table Simon Shaw Excel Discussion (Misc queries) 3 July 18th 07 02:58 PM
suppress zeros in a pivot chart klp Excel Discussion (Misc queries) 0 July 18th 07 02:56 PM
Calculated Field and Calculated Item in Pivot Table Fred Smith Excel Discussion (Misc queries) 0 March 4th 07 09:15 PM
pivot table formulas for calculated field or calculated item Vicky Excel Discussion (Misc queries) 3 June 6th 06 05:06 AM


All times are GMT +1. The time now is 10:59 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"