AVERAGEIF Returns #DIV/0

Question

I have looked around for the answer and can't find it (even though lots of people are having problems with this) - if someone's seen the answer somewhere, please let me know.

I'm doing a very very basic travel expenses sheet

Month by month, I need to go through a column and find a type of expense occurred that month - and then tally it up with all the expenses of the same type. For a monthly total.

Then i've got a column that needs to find out what was the average spend for that type of expense that month. So i can track month-on-month improvement

example - http://f.cl.ly/items/1i3Z0Q0m1V2F2h2B3c2b/Screen%20Shot%202014-12-09%20at%2018.28.47.png

The SUM bit is easy

SUMIF(D2:D300, 'Food', A2:A300)

so, go through 'D' column, find 'Food', and if you find it: give me the value for the same row on 'A' Column, and add them all together..

The Averaging is a bit tricky

i tried

(H4*12)/365

Which basically multiplies the monthly value by 12 and divides it to find out the daily average for the year (or the yearly average?).

But that averages a single input from one month over a year (taking into account months in the future which are zero) therefore it brings the average off :(

What i want to find out is

'What was the daily average ammount I spent getting Food, in January?'

the tricky bit, obviously, is because in the Expense type column there are values i have to discard, so i don't average the food with the accomodation

so i tried

AVERAGEIF(D2:D19, 'Food', A2:A19)

which basically says go through 'D' column, find 'Food', and if you find it: give me the value for the same row on 'A' Column, and average all those values..

which works great for the items i've spent money on, but - if i havent spent anything that month (let's say on Tips) it gives me back an error rather than just '0'

Answer 1

Try IFERROR(AVERAGEIFS(A2:A19,D2:D19,'Food',A2:A19,"<>"),0)

This will average everything in Column 'A' where Column 'D' is 'Food' and the value in Column 'A' is not blank.

If you're trying to get an average over all months even if you didn't spend anything in a given month, though, you might want to use '0' instead of just a blank value.

For example, if I spend $10 in January, $10 in February, $0 in March and $10 in April, my average SHOULD be $30/4 months or $7.50/month. If I leave March blank instead of '$0', The average will be $30/3 months or $10/month.

It all depends on how you need to frame your expenses. If it's 'What was my average over those four months?', you should use a '0' value and you can just use AVERAGEIF. If it's 'What was my average in months that I had expenses?', you can leave it blank and use the AVERAGEIFS formula above. If there are no values at all, it'll display a '0' instead of an error, as was mentioned earlier.