# Calculations from Pivot Table data

1. ## Calculations from Pivot Table data

The attached example contains a pivot table. In the data area ("MOS2") the headings are months of the year that contain expense or encumbrance information. What I want to do is get an average of the past 3 months (so, in this example, months 2-4). I'll be doing this for multiple pivot tables in multiple workbooks on a monthly basis.

As you can see in the example, not all months appear (if nothing happened, they don't show). So, I need a macro that will first look at the months in the pivot, decide if any of them are from the last 3 months, and if so, add those three months and divide by 3 to get an average. I can do the last part of that, but don't know how to determine which months belong to the last 3 months, so would appreciate some help with that.

Next step (if anyone wants to tackle it) is to do the annual projection; basically, the average times 12, or if that's less than the Total (which is the YTD), put the total.

John

2. ## Re: Calculations from Pivot Table data

John

The best way I can think of is to add an extra column (column A in my example) on the source sheet (Sheet3) with a formaula =IF(MONTH(TODAY())-C2<=3,G2,0)). This will include the monetary amount if the month is within 3 months of the current month. You should then pivot on this calculated column and Average instead of Sum.

See your file attached.

Dion

3. ## Re: Calculations from Pivot Table data

Dion,

I can post zipped files from work, but I can't open zipped files from work. I know it's weird, but because of this I can't see your example, so don't know what's in C2 or G2 for your formula.

I'm thinking maybe a Choose or an IF formula would work. There's a range of 3 to 12 months showing (we'll always have at least 3), and there are only 3 months applicable at projection-writing time. So, IF the value in the data range falls into one of these three months, add it, otherwise ignore. Take the result, divide by 3, there's your average.

I can come up with a formula to determine the applicable 3 months, but don't know how to check for those in the Pivot Table and perform the calculation (but I can probably figure the calculation part).

4. ## Re: Calculations from Pivot Table data

All I did was insert a column at column A and insert the formula I gave above. So cells C2 and G2 are what used to be B2 and F2 on your original sheet. Then amended the data field in the pivot table to average (not sum) column A.

The problem with doing it your way is if in a 3 month period there's 1 month where there's no spend, then the average will be divide by 2.

5. ## Re: Calculations from Pivot Table data

Dion,
I've tried your formula as described, and it's not giving the results I anticipate. For instance, your formula basically says
``Please Login or Register  to view this content.``
I'm interested only in the last 3 months, so going by today's numbers month 9 would satisfy the requirement of current month - C month being less than 3 (5-9=-4), but it's not one of the last 3 months.

Going to "if in a 3 month period there's 1 month where there's no spend, then the average will be divided by 2", whether there's 1, 2 or 3 months with expenses in the last 3 months, we'd always divide by 3. If expense is 30 in month 1, 0 in month 2, and 0 in month 3, the average would be 10.

6. ## Re: Calculations from Pivot Table data

You're right. How about:

=IF(AND(MONTH(TODAY())-C2<=3,MONTH(TODAY())-C2>0),G2,0)
(less than 3 AND greater than 0).

Dion

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1