I'm trying to find a way to aggregate average values in a Google sheet in a pivot table using calculated fields.
Here's my data:
| Close Date | Amount |
|---|---|
| March 2020 | £60,000.00 |
| April 2020 | £15,000.00 |
| April 2020 | £90,000.00 |
| April 2020 | £220,000.00 |
| April 2020 | £50,000.00 |
| May 2020 | £100,000.00 |
| May 2020 | £440,000.00 |
| May 2020 | £15,999.00 |
| May 2020 | £85,000.00 |
| May 2020 | £500,000.00 |
| June 2020 | £270,000.00 |
| June 2020 | £210,000.00 |
| July 2020 | £60,000.00 |
| July 2020 | £35,000.00 |
| July 2020 | £75,000.00 |
By using the following calculated field, I can calculated each month's average:
=AVERAGEIFS(B2:B17,A2:A17,">="&Date,A2:A17,"<="&EOMONTH(Date,0))
| Close Date | Average Result |
|---|---|
| March 2020 | £60,000.00 |
| April 2020 | £93,750.00 |
| May 2020 | £228,000.00 |
| June 2020 | £240,000.00 |
| July 2020 | £56,666.67 |
However, what I want to do is cumulate the average values like so:
| Close Date | Cumulated Average Result |
|---|---|
| March 2020 | £60,000.00 |
| April 2020 | £153,750.00 |
| May 2020 | £381,750.00 |
| June 2020 | £621,750.00 |
| July 2020 | £678,416.67 |
I realise I could add an additional formula column in the sheet itself, but I'd much prefer to do this via a calculated field if possible.
Many Thanks
CodePudding user response：
Since you have tagged both Excel and Sheets, the solution in Excel is to merely select to show the values of the Average column as a Running total (in the Value Field Settings tab).
CodePudding user response：
cumulative running average:
=ARRAYFORMULA({UNIQUE(TEXT(FILTER(A1:A, A1:A<>""), "mmmm e")), MMULT(TRANSPOSE((
SEQUENCE( COUNTUNIQUE(TEXT(FILTER(A1:A, A1:A<>""), "e-m")))<=
SEQUENCE(1, COUNTUNIQUE(TEXT(FILTER(A1:A, A1:A<>""), "e-m"))))*
QUERY({TEXT(A1:A, "e-m"), B1:B},
"select avg(Col2) where Col2 is not null group by Col1 label avg(Col2)''")),
SEQUENCE( COUNTUNIQUE(TEXT(FILTER(A1:A, A1:A<>""), "e-m")), 1, 1, 0))})
