Computing Averages For Daily Sales

[SD]

Hello-

I store all my sales data in a microsoft excel sheet. I was wondering
how I can automatically have the daily average sales displayed at the
bottom of the sheet without having to manually compute it each day.

For example, if today is the 7th of May and I look at my sheet after
entering in the sales for yesterday and there are 31 days in this
month, what function do I use to compute the average at the bottom so
the computer automatically knows each day that I want to take the days
of the month thus far (as of yesterday, that would be 6) and divide it
by the revenue already summed up month to date (thats easy)..

Currently, I manually have to break out the calculator...divide the
MTD sales by 6 and multiply it by 31 to forcast my monthly average
sales..

What I would like to do is have a cell automatically do this for me
without me having to tell excel that there is 6 days in the month,
etc. I hope this is clear

Any Suggestions Would Be helpful.

Thanks
SD.

 

[Gaurav]

Can you post some example of how the data is placed in the worksheet?

 

[John Bundy]

You need the analysis toolpak add-in in place (Tools->Add-Ins) then in a cell
somewhere put this formula:
=EOMONTH(MONTH(NOW()),0)
this returns the number of days in the current month.
In another cell enter this formula:
=DAY(NOW())
this tells you the current day
Now you just need a sum formula that sums the month so far and divides it by
the second formula for your average. Note that you might want to do
=DAY(NOW()-1) if you will be looking at it the next day. For the forecasting
peice, just subtract the second formula from the first to get days remaining
and multiply your average by this.
So, if formula 1 is in A2 and formula 2 is in B2 and all of you daily sales
are in D1-D16 then the forecast would be:
=(SUM(D116)/B2)*(A2-B2)

 

[SD]

You need the analysis toolpak add-in in place (Tools->Add-Ins) then in a cell
somewhere put this formula:
=EOMONTH(MONTH(NOW()),0)
this returns the number of days in the current month.
In another cell enter this formula:
=DAY(NOW())
this tells you the current day
Now you just need a sum formula that sums the month so far and divides it by
the second formula for your average. Note that you might want to do
=DAY(NOW()-1) if you will be looking at it the next day. For the forecasting
peice, just subtract the second formula from the first to get days remaining
and multiply your average by this.
So, if formula 1 is in A2 and formula 2 is in B2 and all of you daily sales
are in D1-D16 then the forecast would be:
=(SUM(D116)/B2)*(A2-B2)

Hello-

I tried what you suggested....I am getting an error saying "?Name". I
went to tools, addin, clicked on "analysis pack"...it didnt ask me for
a CD or anything...this is for a MAC btw...what could be going on?

 

[SD]

Hello-

I tried what you suggested....I am getting an error saying "?Name". I
went to tools, addin, clicked on "analysis pack"...it didnt ask me for
a CD or anything...this is for a MAC btw...what could be going on?

Okay I got that figured...

Here is the next issue...the projected averages at the end of the
month...Each sheet will continue on and compute averages after the
month closes based on whatever the current date is...how do i stop
that from happening..is there an if statement that I can use?

Thanks
J.J.