Average # of sales per day in a specific month when the month is not yet over

[maxedison]

I am trying to keep track of the average sales per day for each month. Since the current month is not yet over, dividing the month's total sales by 30 obviously does not tell me how many sales per day I am so far averaging. Is there a way to make Excel take account of the fact that the month is not over yet and thereby give an accurate calculation? For example, let's consider a month that only has 10 total days (for the sake of being concise)

# of sales per day:
1, 1, 3, 2, 4, 1, x, x, x, x

The letter x represents days which we have not yet reached in the month, and therefore should not be included in Excel's calculations. Can I somehow input a formula that, using the example data, will currently only divide the total sales by 6, but that will automatically divide the total by 7, 8, 9, and 10 as those days are reached? I imagine this would have something to do with Excel recognizing that there is no data in the cells for those final 4 days, and consequently knows to only divide the total by 6 at the moment.

Thanks for the help!

[timbo1957]

Please Login or Register  to view this content.

Divide your sales by A2 and you will always have your average sales per day based on the current day of the month.

Hope that helps.

[daddylonglegs]

If the days without sales are blank (not zero) then AVERAGE function will ignore these so you can just use

Please Login or Register  to view this content.

This will give a correct average whether one day has data or all 31

[maxedison]

daddylonglegs,

your solution works only if you have just one row of data that you are averaging (as i did in my example). if i have multiple products that i am selling and i want to know average total of all products sold each day, the average function doesn't work because it will average the number based on how many cells are selected, rather than just the number of days (represented simply by the number of columns assuming that you have the days going horizontally). obviously the example i gave wasn't the best one.


timbo,
your solution works great!


thanks to both of you for your help!

[Bjornar]

your solution works only if you have just one row of data that you are averaging (as i did in my example). if i have multiple products that i am selling and i want to know average total of all products sold each day, the average function doesn't work because it will average the number based on how many cells are selected, rather than just the number of days (represented simply by the number of columns assuming that you have the days going horizontally). obviously the example i gave wasn't the best one.

The AVERAGE function should work for multiple rows/columns too. It is averaging based on the number of cell containing numbers. Including zero!
A blank cell or a cell with text is not included in the averaging.

[maxedison]

timbo,

the only problem with the solution you provided is that, once the month comes to an end and the next one begins, i need to change the formula of the previous month so that it is being divided by a stable # (28, 30, or 31 depending on the # of days in the month). Otherwise, it will start getting divided by what day it is in the new month.

any idea how to solve this problem?

regardless, thanks again.

[maxedison]

Bjornar,

Perhaps my explanation wasn't clear enough. Take the following sample data, for example. Each column represents a new day.

Product 1: 1, 4, 2, 3, 5
Product 2: 4, 2, 6, 1, 0

If I want to know the average number of products sold per day (regardless of which product it was), I can't just have Excel perform the average function over all those data cells because it will divide the total sum by 10, rather than 5. Is there a way to make the Average forumla only consider the number of columns (and therefore days)?

[Bjornar]

You can just multiply the result from the AVERAGE function by the number of rows.

If 5 productrows:

Please Login or Register  to view this content.

Will this do for you, or do you keep adding products so you need a more dynamic formula ?

[daddylonglegs]

timbo,

the only problem with the solution you provided is that, once the month comes to an end and the next one begins, i need to change the formula of the previous month so that it is being divided by a stable # (28, 30, or 31 depending on the # of days in the month). Otherwise, it will start getting divided by what day it is in the new month.

any idea how to solve this problem?

regardless, thanks again.

I'm not quite sure how your data is set out but assuming the following

Today's date is in A1. For each month you have the first day of the month, e.g. 1-nov-07 in cell C2 and sales for the month in D2.

To divide sales by the appropriate day try

Please Login or Register  to view this content.