Calculate an average daily expense cell

Hi

I assume you are using COUNTA for your formula, instead try

Please Login or Register  to view this content.

.

Regards

Jeff

Hi guys. Thanks for your help. Please believe me I'm a complete noob so things like COUNTA and COUNTIF mean nothing to me I think what you're suggesting is to put in a formula that only counts the entry if it is larger than "0". (I could be completely wrong on this). I actually do want to include the "0" entries. For example if I buy nothing on a particular day I want my expense/day to drop. The problem is that my whole range for the month is already filled in with zeros so right now my daily expenses is listed at 240 yen per day when actually it's well over 1000. So by the end of the month my expense average will be accurate but the rest of the month will be completely inaccurate.

Is there any way that I can have a formula that calculates the sub-totals for the day but have it leave the cell blank until I've actually entered some data?

And again, you may have already pointed me in the right direction but I'm too dim to realize it. Please be patient with me

Just thinking some more, I suppose I could switch the days that I don't spend any money to have a value of 1 (one yen is practically nothing!) and that would be almost the same.

Well I tried what you suggested Richard and my idea of just putting one into the column and it seems to work fine. It still seems slightly dirty to me-there must be a better solution but I guess in the grand scheme of things, realistically there will be extremely FEW days where I spend no money on food and for those few days, one yen out of 25-30,000 yen in a month won't make much of a difference.

So I guess I'll live with it! Thanks very much for the help guys. If there is another solution I would still be interested in hearing it, just out of curiosity sake.

if you want to average counting days when you spend 0
=SUM(B10:B100)/COUNTIF(B10:B100,">=0")
but you must enter 0 in cell

Yes but the problem is that all the cells automatically have 0 entered in that range because I copy and pasted =SUM(C1:E1) for all of the days in the month. So even though it's only Feb 7th, the entire range has 0 in the expense column for each particular date. I could erase that formula and just have a blank cell but then I would automatically be adding up my day's expenses and inputting them rather than having the program do the calculation for me. Until I can get a better solution I guess I'll just keep it so that it calculates >0 and put in a "1" for the days that I don't spend anything to keep the average accurate.

I just made up a quick example. I'm actually using office mobile on my PDA and this is Open office but it's pretty much the same

So you can see that since I've copy and pasted the formula =SUM(C1:E1) (which adds all the subtotals for me) through the entire range of 1 month, it has automatically made everything in that range 0 even though I haven't actually entered data for those other days yet. For the average in this example I just did a simple =AVERAGE(B1:B31) . Now if the days that haven't occurred yet had no data then this would be fine as I believe (I don't actually know) that excel would only count the days which have some data. But you can see that it has counted all the days of the month and so my daily average expenses are only 160 yen per day when actually it should be well above 1000. Now I've managed to get around that by making the numbers counted greater than zero and adding a "1" for the days I don't spend money, but surely there's a better way.

http://i59.photobucket.com/albums/g2...r907/excel.jpg

Oops it appears I can't post images on this forum. Well that is an image of the example I made up.

Hi guys, thank you for your continuing interest in this thread. I'm going to upload a workbook now. On my PDA (which has Excel Mobile) your formula worked fine Richard but for some reason when I try to replicate it on Open Office I can't get it to work! So my average total just has a simple =AVERAGE(Bx:Bx). Yes I do want to count the zero's but only for the dates that have already passed. What I wish is that the zero's would not be there in the first place. If there is a day when I don't spend any money then I will manually put "0" into the cell. What I would like for my expense column is something like

Please Login or Register  to view this content.

And broro, wow. I described my spreadsheet as "simple" but after looking at yours I see I was being WAY too generous. Mine is stone age!

Also just to clarify, I want historical 0's to be counted not future ones. Also, will excel be able to tell whether or not that day has occurred already? Again, if excel would simply just not enter any numbers into the expense column until I have actually entered some data then I would have no problem.

Thanks Richard-I'll give that a shot later today.

Yes that worked brilliantly! I no longer have zeros in my expense column and now I can calculate the average using the simple average formula. That's exactly what I wanted. If only I could have explained myself better from the beginning. Thanks for your help guys.