• 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 - calculation field for Forecast at completion

R

Ram Gouda

New Member
Hello all,

I am newbie here and struggling to find a solution for an excel calculation in Pivot tables.

I have a simple pivot table with the source data having the information about Month, Project, Workstream, task name,Org ID, resource, planned hours, actual hours, planned cost and actual cost. There are three report filters Month, Project ID, Workstream ID on the pivot table.

I need to create two calculated fields 'Forecast at completion-hours' 'FAC - Cost', on the pivot table. Is it possible by using calculated fields in Pivot tables?
I am looking for two solution options.
1. based on the current month, the formula should pick actual up to current month and add the planned cost for rest of the year. e.g for june, FAC = actual upto may+planned from June to DEC.
2. If possible, create a drop down to allow the users to chose the month they want to see the FAC and use that month value to calculate the FAC.

Could you please advise me how to go about it? Any help is massively appreciated.

Attached is the sample file with data and the pivot table.

..Ram
 

Attachments

Hi Satish,

Thanks for your reply. Although it was not working completely as expected but it really gave me a good platform to explore and fix the issue.

The formula you gave was missing just an additional condition. This is how changed it.
SUMPRODUCT((--(Data!$E$2:$E$40=$A6))*(--(Data!$A$2:$A$40<=6))*(Data!$H$2:$H$40))+SUMPRODUCT((--(Data!$E$2:$E$40=$A6))*(--(Data!$A$2:$A$40>6))*(Data!$G$2:$G$40))

If any one interested I just want to share my two cents of knowledge I gained while researching for this problem.
1. Cannot use sumproduct in pivot table calculated fields
2. I am using the above formula along side my pivot table rather than in source data
3. use -- to evaluate TRUE, FALSE in an array formula to convert to 1,0 which speeds up the execution of formula.

Apologies if most of you guys already know this, but I just stumbled in to this wisdom and thought of sharing.

Thanks again for taking time in replying to my post. Much appreciated.
 
You must log in or register to reply here.
Back
Top