• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot Table Calculated Field - % error #DIV/0!

P

Pitcher

Member
Hi guys,


Hoping someone can help me...


I have multiple pivot tables that include calculated fields. What I try to do in many of my pivots is understand whether TY is up or down on last year by inputting the following formula =(TY-LY)/LY in the calculated field. The issue arises when TY has a value and LY does notwhich results in the error #DIV/0!.


Is there a way around this? I can share worksheet if needed.


Many thanks,

Andrew
 
Hi Andrew ,


Check this link :


http://social.technet.microsoft.com/Forums/en-US/excel/thread/cddae0be-94fd-4d98-b134-8fbeecc87c51


Narayan
 
Thanks Narayan,


The current formula is =('TY Units' -'LY Units' )/'LY Units'. What would the if formula look like using with regard to the above?


Thanks,

Andrew
 
Hi Andrew ,


I am reproducing the answer from the posted link :



Since the error handling doesn't work in calculated fields (such as iferror), you can just go to the pivottable options (by right clicking on the pivot table) and then in the layout & Format tab under the format section, tick the box "for error values show:" and enter what you want shown - such as leaving blank or putting a character or word etc..




Narayan
 
Thanks Narayan,


Understanding I can do as you mentioned and have those cells which show the errror sign as blank, the issue I then face is that the overall total then appears blank also.


I have multiple products in row labels and when I want to understand a customer's overall performance and not specific products I cannot provide a % var only a numeric variance.


Is there a way around this?


Appreciate your help on this.


Regards,

Andrew
 
Hi Andrew ,


The value to be shown in the case of an error need not be blank ; if you want you can assign the value 0.


Can you upload your workbook ? I am not able to understand what the specific requirement is.


Narayan
 
Hi Andrew ,


Unfortunately , this forum does not have any provision for uploading files or attaching files to posts.


What you can do is to use your preferred file-sharing website such as RapidShare , SpeedyShare , 2shared , Googledocs , SkyDrive , DropBox , FileConvoy , ZippyShare to upload your file , give others permission to download and open the file , and then post that access link here in this same topic.


Which ever website you use , please ensure that it does not require anyone who wants to download your file to register at the website.


Narayan
 
Good day Pitcher


Green stickys at top of home paqe


http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Hi,


Try using the positional attributes in the calculated items to test for 0 or other errors as in:


=IF(FY[-2]=0,0,(FY[-1]-FY[-2])/FY[-2])


It avoided the division by zero reference. It did force a turn off of the automatic sorts however.
 
Thanks Faseeh / Bobodj,


Faseeh - not too sure what you meant by your comment. Happy to upload a file anywhere if you can help, can you please confrim?


Many thanks,

Andrew
 
Hi, Pitcher!

Consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.

Regards!

PS: I think that Faseeh tried to mean that the file weren't huge.


@Faseeh

Hi!

All files weights in Kbs, since you didn't specified an upper limit they might weight a million Kbs. Want one of 1Gb? :)

Regards!
 
@ Pitcher,


Just wanted to say that "consider uploading a file of smaller size" meant nothing else. :)

BTW in your sample sheet, Column E does't show Div. Error any where?


@ SirJb7


Thanks for explaining my point. :)
 
You must log in or register to reply here.
Back
Top