2

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?

player0
  • 124,011
  • 12
  • 67
  • 124
Zack Macomber
  • 6,682
  • 14
  • 57
  • 104
  • Someone has flagged this question should be closed. Could you please let me know why as I'm genuinely looking for an answer to this problem? Is SO the wrong place for this question? – Zack Macomber Feb 07 '12 at 15:19

1 Answers1

3

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.

Community
  • 1
  • 1
Guy
  • 12,388
  • 3
  • 45
  • 67
  • I think I may go the opposite direction and sum all POSITIVE values for a category and get the average of those now that I'm thinking about this...on my pivot table, do you know how I could get an average of all the positive values for a category? – Zack Macomber Feb 07 '12 at 17:57
  • I've updated the answer to demonstrate the multiple criteria of a pivot table – Guy Feb 08 '12 at 05:26
  • I used "=ARRAYFORMULA(AVERAGE(((Transactions!$A:$A)=$A8) * ((Transactions!$D:$D)>0) ))" to try and get the average of all of my category "Food" (cell A8) transactions that are greater than 0 and that brought back an average of 0.0169. The average of all my positive Food transactions should be about 155 (140, 150, 175 are my positive Food transactions). Any ideas? – Zack Macomber Feb 08 '12 at 14:07
  • You need to add the 3rd argument of the function (Transactions!$D:$D), otherwise you are counting cells instead of summing values of cells. I also edited the formula to use SUM/COUNT as AVERAGE didn't work because it took all blank cells as 0. I believe it should work for you now. – Guy Feb 08 '12 at 17:12
  • OK - think it's just about there - one more issue is that I have headers on the Transactions sheet. When I plug in "=SUM(ARRAYFORMULA(((Transactions!$A:$A)=$A8) * ((Transactions!$D:$D)>0) * (Transactions!$D:$D) )) / COUNT(ARRAYFORMULA(((Transactions!$A:$A)=$A8) * ((Transactions!$D:$D)>0) * (1) ))" I get "error: Not a Number: AMOUNT" - I suspect that is referring to D1 on Transactions which has the value of "AMOUNT" on it. Anyway to tweak this function to ignore that cell but do all the others in column D? – Zack Macomber Feb 08 '12 at 17:35
  • Sure, change from A:A to A2:A – Guy Feb 08 '12 at 17:41
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/7472/discussion-between-zack-macomber-and-guy) – Zack Macomber Feb 08 '12 at 17:44
  • Guy - this is just about there - thanks so much for all your help! Could you join me in a chat (click on the link above in "let us continue this discussion in chat") – Zack Macomber Feb 08 '12 at 17:53