Average multiple months

[Luigi802]

How can I average the total amount I spent on 1 item, but only the months that I spent money on it, for example, Let's say I spend 100 on clothes in jan, 35 in March, 62 in April, 50 in August......and so on I want to only average the amount I spent in the months I spent it. Like right now we are in December, if I just take the total divided by the total amount of months I'd be dividing by 12, when I only want to divide by 4 because I only bought clothes 4 out of the 12 months.........does that make sense?

[63falcondude]

You may be able to use an AVERAGEIF formula if your data is set up correctly.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Luigi802]

You may be able to use an AVERAGEIF formula if your data is set up correctly.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

OK, I hope this works

[63falcondude]

I now see your data, but it appears that you have left out the desired results (the bold part of post #2).

Can you update your sample to include the expected outcome?

[Luigi802]

I now see your data, but it appears that you have left out the desired results (the bold part of post #2).

Can you update your sample to include the expected outcome?

OK I hope this helps

[63falcondude]

Try this:

=SUMIF(Table3[Category],F1,Table3[Amount])/SUM(--(FREQUENCY(IF(Table3[Category]=F1,DATE(YEAR(Table3[Date]),MONTH(Table3[Date]),1)),DATE(YEAR(Table3[Date]),MONTH(Table3[Date]),1))>0)) Ctrl Shift Enter