+ Reply to Thread
Results 1 to 6 of 6

Calculations from Pivot Table data

  1. #1
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    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.

    Thanks in advance,
    John
    Attached Files Attached Files
    Last edited by Mordred; 08-30-2011 at 01:10 PM.

  2. #2
    Forum Contributor
    Join Date
    05-09-2009
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    315

    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
    Attached Files Attached Files

  3. #3
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    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. #4
    Forum Contributor
    Join Date
    05-09-2009
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    315

    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. #5
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    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. #6
    Forum Contributor
    Join Date
    05-09-2009
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    315

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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