Help with formula adding pivot data

[dem86]

Hi all,

I am looking for a little help with my formula. I received input from another board on how to write it but it doesn't seem to be working correctly.

I have a pivot table where only 1 condition out of the 3 that I am trying to add together has data. It is returning a 0 and not 134 like I would expect.

Any help would be appreciated.

=LOOKUP(9.99E+307,CHOOSE({1,2},0,GETPIVOTDATA("Total_Cost",'Summary Financial'!$A$3,"Req_project_code","11-005","Time_step_num","002","Month",2)+GETPIVOTDATA("Total_Cost",'Summary Financial'!$A$3,"Req_project_code","11-005","Time_step_num","003","Month",2)+GETPIVOTDATA("Total_Cost",'Summary Financial'!$A$3,"Req_project_code","11-005","Time_step_num","005","Month",2)))

I am trying to prevent the ref error if data does not exist in all or any of the 3 areas.

Thanks again.

Doug

 

[DonkeyOte]

If any of the GETPIVOTDATA calls returns a #REF! error then the result will be 0 (given 1+#REF! = #REF!)

You need to either:

a) add a handler to each GETPIVOTDATA call (so each returns 0 rather than #REF!)

or

b) if you expect only one of the calls to ever return a result I'd suggest running the calls separately, ie:

=LOOKUP(9.99E+307,CHOOSE({1,2,3,4},0,GETPIVOTDATA("Total_Cost",'Summary Financial'!$A$3,"Req_project_code","11-005","Time_step_num","002","Month",2),GETPIVOTDATA("Total_Cost",'Summary Financial'!$A$3,"Req_project_code","11-005","Time_step_num","003","Month",2),GETPIVOTDATA("Total_Cost",'Summary Financial'!$A$3,"Req_project_code","11-005","Time_step_num","005","Month",2)))

to reiterate though - if you need to aggregate at any time then the above is not viable.

 

[dem86]

Hi and thanks for the quick reply.

The situation I have is that I could have 3, 2, 1 or none that return #Ref.

I was using If(iserror but my formula was too long which is why I was recommended to use the method I posted here.

I guess it is back to the drawing board.

Doug

 

[DonkeyOte]

I was using If(iserror but my formula was too long which is why I was recommended to use the method I posted here.

Yes I think a recall a post along these lines previously (elsewhere)

Regards point a) from my prior post (given option b is precluded)

=LOOKUP(9.99E+307,CHOOSE({1,2},0,GETPIVOTDATA("Total_Cost",'Summary Financial'!$A$3,"Req_project_code","11-005","Time_step_num","002","Month",2)))+LOOKUP(9.99E+307,CHOOSE({1,2},0,GETPIVOTDATA("Total_Cost",'Summary Financial'!$A$3,"Req_project_code","11-005","Time_step_num","003","Month",2)))+LOOKUP(9.99E+307,CHOOSE({1,2},0,GETPIVOTDATA("Total_Cost",'Summary Financial'!$A$3,"Req_project_code","11-005","Time_step_num","005","Month",2)))

Longer than before but still shorter syntax wise than using double evaluation - we're assuming of course you're required to use with versions prior to XL2007 - if not use IFERROR rather than the LOOKUP construct above.

 

[dem86]

unfortunately i am still on 2003.. but this work great...

Thanks again!