How do I work out an average spend?
May 21, 2009 12:07 PM Subscribe
ExcelFilter: I want to calculate an average food spend, but the days I buy food are irregular throughout the month. How can I draw up a table/graph that gives an average based on a date and amount (for OpenOfficeCalc)?
My best idea was to divide each spend by the days in between, but surely there is a better way to do this?
I'd like to end up with a graph with the date along the bottom too, but even that defeats me.
Please hope me!
My best idea was to divide each spend by the days in between, but surely there is a better way to do this?
I'd like to end up with a graph with the date along the bottom too, but even that defeats me.
Please hope me!
Here's my idea: keep a log of all the food you eat each day and price it out accordingly. For instance, if you buy chicken fillets for $4 and you use half for dinner - mark that down for $2 spent. I keep a daily excel log of all the food I eat just to keep my calorie intake in check and I don't think it would be very hard to assign a dollar value to each meal.
posted by any major dude at 12:25 PM on May 21, 2009
posted by any major dude at 12:25 PM on May 21, 2009
Response by poster: That looks like a great place to start, thanks. It'll take me a little while to parse the formula-heavy stuff, but I'll give it a go. As far as I can tell this is more or less the same question, the only difference being that I'm using OpenOffice.
posted by Gisela at 12:27 PM on May 21, 2009
posted by Gisela at 12:27 PM on May 21, 2009
Response by poster: Do you want a monthly average?
Sorry, yeah, I should have been clearer. I'm looking for a monthly average spend.
posted by Gisela at 12:36 PM on May 21, 2009
Sorry, yeah, I should have been clearer. I'm looking for a monthly average spend.
posted by Gisela at 12:36 PM on May 21, 2009
Response by poster: I've just calculated an average day based on the amount spent on food divided by the number of days that food lasts. I've added those figures to make an average monthly spend. Is that needlessly long-winded?
posted by Gisela at 1:12 PM on May 21, 2009
posted by Gisela at 1:12 PM on May 21, 2009
I'm pretty sure I know what you're asking; I think this shouldn't be too hard. Here is the formula:
=(SUM(A1:A31))/(COUNTIF(A1:B15, ">0"))
…where A1:B15 is the range of cells (that is, A1 is the first cell, and B15 is the last cell, and everything in between is included) that has all of your food buys in it.
What this formula says:
“Take the sum of all my food buys for the month [SUM(A1:B15)] and divide it by the number of food buys per month, which you get when you count the number of food buys that were greater than zero [COUNTIF(A1:B15, ">0")].”
Does that make sense?
posted by koeselitz at 2:58 PM on May 21, 2009
=(SUM(A1:A31))/(COUNTIF(A1:B15, ">0"))
…where A1:B15 is the range of cells (that is, A1 is the first cell, and B15 is the last cell, and everything in between is included) that has all of your food buys in it.
What this formula says:
“Take the sum of all my food buys for the month [SUM(A1:B15)] and divide it by the number of food buys per month, which you get when you count the number of food buys that were greater than zero [COUNTIF(A1:B15, ">0")].”
Does that make sense?
posted by koeselitz at 2:58 PM on May 21, 2009
…sorry, I should have said: that computes the average amount of money spent per shopping trip, no matter how many shopping trips you do in a month. That is what you wanted, right?
posted by koeselitz at 3:02 PM on May 21, 2009
posted by koeselitz at 3:02 PM on May 21, 2009
On review, I actually don't know what you want. You say you want to see "an average based on a date and an amount," and add that you're looking for "monthly average spend." Are you looking for:
…a chart showing the amount you spend every month over, say, a year?
…a chart showing the average amount you spent per shopping trip every month over a year?
…a chart showing the average amount you spend per month in several different years?
Do you see my difficulty? When you say “monthly average,” I don't know if you mean 'the average I spend per month' or 'the average I spend per shopping trip in a given month.'
posted by koeselitz at 3:09 PM on May 21, 2009
Do you see my difficulty? When you say “monthly average,” I don't know if you mean 'the average I spend per month' or 'the average I spend per shopping trip in a given month.'
posted by koeselitz at 3:09 PM on May 21, 2009
Response by poster: …a chart showing the amount you spend every month over, say, a year?
Sorry for the confusion, but yes, this is what I was after. I think I understand what you were suggesting but that syntax doesn't seem to work in OpenOffice, so I can't be sure.
posted by Gisela at 3:31 PM on May 21, 2009
Sorry for the confusion, but yes, this is what I was after. I think I understand what you were suggesting but that syntax doesn't seem to work in OpenOffice, so I can't be sure.
posted by Gisela at 3:31 PM on May 21, 2009
For reference, the only difference between OpenOffice Calc syntax (which is what I use too) and MS Excel syntax is that Excel uses commas as separators whereas Calc uses semicolons. So if you just replace every "," with a ";" (minus quotation marks) then it should work.
So you want to know how much you spend every month. Keep in mind: this isn't an average, it's an exact amount: the sum of all purchases during the month. You can, of course, average all the months and get a single average monthly cost of food, but there will only be one average then.
I would do this:
1. Set up a sheet with a different cell for every day of the month. It could look like a calendar, like this:
|Mon Tue Wed Thu Fri Sat Sun|
|---|---|---|---|---|---|---|
|---|---|---|---|---|---|---|
|---|---|---|---|---|---|---|
|---|---|---|---|---|---|---|
|---|---|---|---|---|---|---|
…or it could look like a single long row, like this:
|----------|----------|----------|----------|----------|----------|----------|----------|----------|----------|
|----------|----------|----------|----------|----------|----------|----------|----------|----------|----------|
| Mon 5/25 | Tue 5/26 | Wed 5/27 | Thu 5/28 | Fri 5/29 | Sat 5/30 | Sun 5/31 | Mon. 6/1 |Tue. 6/2 | Wed. 6/3 |
…or however you prefer it. If you like, you can put each month on a different sheet; or you can have them all in different places on the same sheet.
2. In every cell, put the amount of money spend on food for that day; if nothing was spent, you don't need to put anything there.
3. Now, to get the total money spent during a month, go to the cell where you want the total to appear and enter this formula:
=SUM(A2:G5)
…where, again, A2 is the cell of the first day of the month and G5 is the cell of the last day.* This will give you the amount of money spent on food for that month.
4. If you want an average amount you spend on food per month, you simply add up the total amount spent on food and divide it by the number of months:
=SUM(H6; H10; H12; H19)/COUNT(H6; H10; H12; H19)
…where H6; H10; H12; H19 are all the cells with the monthly totals that you just calculated above.
I hope this helps.
__________________________________________________________________________________________________________________________________________________
*If you're putting your different months on different sheets (click the tabs at the bottom of the graph to go to the different sheets) then you don't just need the cell reference but the sheet reference, too. That's no problem: you can get it automatically. Just start typing the formula, and then, when you want to enter the reference, click over to the sheet you want and click on the cell you're referring to; it will automatically enter the reference in for you, so you don't even have to type it.
posted by koeselitz at 4:11 PM on May 21, 2009
So you want to know how much you spend every month. Keep in mind: this isn't an average, it's an exact amount: the sum of all purchases during the month. You can, of course, average all the months and get a single average monthly cost of food, but there will only be one average then.
I would do this:
1. Set up a sheet with a different cell for every day of the month. It could look like a calendar, like this:
|Mon Tue Wed Thu Fri Sat Sun|
|---|---|---|---|---|---|---|
|---|---|---|---|---|---|---|
|---|---|---|---|---|---|---|
|---|---|---|---|---|---|---|
|---|---|---|---|---|---|---|
…or it could look like a single long row, like this:
|----------|----------|----------|----------|----------|----------|----------|----------|----------|----------|
|----------|----------|----------|----------|----------|----------|----------|----------|----------|----------|
| Mon 5/25 | Tue 5/26 | Wed 5/27 | Thu 5/28 | Fri 5/29 | Sat 5/30 | Sun 5/31 | Mon. 6/1 |Tue. 6/2 | Wed. 6/3 |
…or however you prefer it. If you like, you can put each month on a different sheet; or you can have them all in different places on the same sheet.
2. In every cell, put the amount of money spend on food for that day; if nothing was spent, you don't need to put anything there.
3. Now, to get the total money spent during a month, go to the cell where you want the total to appear and enter this formula:
=SUM(A2:G5)
…where, again, A2 is the cell of the first day of the month and G5 is the cell of the last day.* This will give you the amount of money spent on food for that month.
4. If you want an average amount you spend on food per month, you simply add up the total amount spent on food and divide it by the number of months:
=SUM(H6; H10; H12; H19)/COUNT(H6; H10; H12; H19)
…where H6; H10; H12; H19 are all the cells with the monthly totals that you just calculated above.
I hope this helps.
__________________________________________________________________________________________________________________________________________________
*If you're putting your different months on different sheets (click the tabs at the bottom of the graph to go to the different sheets) then you don't just need the cell reference but the sheet reference, too. That's no problem: you can get it automatically. Just start typing the formula, and then, when you want to enter the reference, click over to the sheet you want and click on the cell you're referring to; it will automatically enter the reference in for you, so you don't even have to type it.
posted by koeselitz at 4:11 PM on May 21, 2009
This thread is closed to new comments.
posted by jessamyn at 12:20 PM on May 21, 2009