Calculating Yearly Projected Amount

[shilabrow]

I have a dataset that I group with Pivot table to get the yearly amount by each Category. However for 2014 - amount to date only reflect January - April. I want to calculate the projected 2014 Total. Can I use Pivot table to achieve this cos I need to Pivot chart it automatically as well. Please help thanks!! Any other suggested help will be useful as well. Much appreciated!!

Category Yr2011 Yr2012 Yr2013 Yr2014
Food 200 250 260 45
Drinks 180 200 240 50
Salad 300 310 280 75
Snacks 400 420 440 110

[gebobs]

I know there are grouping functions that others more versed in pivots can tell you about. Not sure if they will address this issue. Otherwise, you could create another column next to 2014 to calculate the projected annual amounts.

So if the pivot is in a1:e5, then the equation in e2 (projected annual for food) would be:
=12*d2/(month(now)).

[shilabrow]

Thanks Gebob. Yeah you're right with the Formula just that I am unable to group that into my pivot to Prepare a Chart for that. I end up using that formula and copying all other details into separate sheet manually before I can table it all for Analysis and charting.

Is there a way to put that formula in Pivot table (Can Pivot do the calculation internally so it can have separate Project Yr Field in the Pivot Field list?)
This way its grouped together in the Pivot table and easier to work with.

Any help and all help will be appreciated.

Thanks Much!!!


By the way Gebob - I notice you said I should put the formula in e2 even though the Pivot is in A1 to E5. Is this even possible? Thanks

[gebobs]

No, I meant F2.

Could you attach your file so I can tinker with it rather than have me recreate it? Who knows? Maybe we can figure it out together.

[shilabrow]

Thanks Gebobs,
Attached is a file. I appreciate your help. (Q: Can Pivot do the calculation internally so it can have separate Project Yr Field?)
This way its grouped together in the Pivot table and easier to work with.

Any and all help are welcome. Much appreciated!!

[gebobs]

I've done a work around. I added a column (I called it Revenue Projection) that does a calculation using the Revenue sum (i.e. if current year, use projection formula, otherwise just sum). Basically, it boosts all the revenue records for the current year so that data is not realistic, but it gives the desired result. Then just pivot on that field. See attached and let me know if this helps at all.

[shilabrow]

Thanks for the help I really appreciate it. As much as that displays the Projected amount. I want the 2014 year to show as 2014 YTD and 2014 PROJ. your formula displays only 2014 PROJ amount. However, I know there is a calculated field function in PIVOT TABLE, can this be used cos I don't have a clue as to how to get around using it.


Any and all help appreciated.

Thanks

[gebobs]

If I'm not mistaken, the calculations in pivot tables are limited to basic statistical functions like SUM, MAX, VAR. As such, I am at my wit's end how you can do this with the canned pivot table.

It can be done, however, by making the table on your own using SUMPRODUCT functions. This is easy, however it would require you to maintain a complete list of product numbers. It seems to me, based on your previous responses, that you want a completely automated solution so I'll leave it to you to design this if you want it or if you need help let me know.

Other than that, I've reached my level of incompetence with regard to pivot tables so it may just be a case that elicits the bit of sage advice that Mick Jagger offered in a song that begins with "I saw her today at the reception, a glass of wine in her hand."