Custom average function on a pivot table in Google Sheets

Question

I have a spreadsheet that I'm starting to use for personal money analysis. My main sheet is called "transactions" and has headers of Category, Description, Date and Amount (it's basically a check register). I've created a pivot report off of that sheet that contains sum, min and max of Amount by Category. I would like to make a custom average function on that pivot report but not sure how to go about it. What I would like to see is the average amount of negative transactions between positive ones. My positive transactions are my paychecks and the negative transactions are any spending I do.

An example might help in what I'm trying to do here...

Let's say for category "Food" I have the following transactions (in this order)... -20 -25 -30 100 -30 -35 -40

I'd like my average to be calculated like this... ( ( (-20 + -25 + -30) / 3 ) + ( (-30 + -35 + -40) / 3 ) ) / 2

Anyone have the slightest idea on how I can enhance my pivot report to do this?

Answer 1

You do it with something like:

=ARRAYFORMULA(AVERAGE(IF(Sheet1!D2:D8<0,Sheet1!D2:D8, 0)))

where column D is the amount of your example and Sheet1 contains the "transactions" of your example.

If you want to fill it for the pivot table (having the category as another criterion) you can check the answer at: https://stackoverflow.com/a/9165254/179529

=SUM(ARRAYFORMULA(((Transactions!$A2:$A)=$A2) * ((Transactions!$D2:$D)>0) * (Transactions!$D2:$D) )) 
/
SUM(ARRAYFORMULA(((Transactions!$A2:$A)=$A2) * ((Transactions!$D2:$D)>0) * (1) )) 

where $A2 is the cell where you have the category name in the pivot table (The $ will allow you to copy the formula to other columns in you want it per month or other second criterion.

If you want to SUM the element in column D only if they great than 0, you need to have ((Transactions!$D2:$D)>0) as the second argument and (Transactions!$D2:$D) as the 3rd argument (otherwise you will count the cells instead of SUM them).

Since AVERAGE will take blank cells as well, I've used SUM/COUNT instead. Note that COUNT is actually SUM with the 3rd argument as 1.

Also note that if you want to ignore a header line you need to define your columns with Transactions!$D2:$D, to start from the 2nd row.