Need a Daily Sales Average Equation

[savvv]

Good morning,
I'm a n00b here but have been using Excel for over a decade. I've got probably what would be considered a tier-above-entry-level accounting spreadsheet that I use for my small business and my wife's. She's doing sales and I want to make a cell for a rolling daily sales average. So, she started selling on January 19th of this year, and I have an Annual Summary page of the sheet that shows total sales for the year. I would like to have this total sales number be divided by the number of days that have passed since January 19th, and be updated daily based on today's date.

What I started to do was go into my List tab, which is normally hidden as it has a lot of my Schedule C (tax form) values that I don't need to see while I input data, and I made a column of dates starting with January 19th and running down as far as I want. Then in the next column, I started with a number 1, then 2, etc. So by using these two columns, I know that today, February 18th, is 31 days in business. So the year end sales number divided by 31 gives us our average daily sales. I just don't know how or which function to use so that it looks at today's date, references these two columns and uses the number for that date as what the total sales is divided by.

Make sense? Thanks!

[AliGW]

To find the number of days in business, you can use this - no helper columns necessary:

=DATEDIF(DATE(2017,1,19),TODAY(),"D")+1

So, say the total sales are in A1, use this:

=A1/(DATEDIF(DATE(2017,1,19),TODAY(),"D")+1)

[Tony Valko]

To find the number of days in business, you can use this

=DATEDIF(DATE(2017,1,19),TODAY(),"D")+1

Using a cell to hold the start date...

A1 = 1/19/2017

=DAYS(TODAY(),A1)+1 (requires Excel 2013 or later)

=TODAY()-A1+1

[savvv]

Awesome! Thank you so much. One more question while we are on a roll here...

If I wanted to see our overall average per day of the week...say we find out that most people make purchases on Thursdays so we then really push it on Thursdays to keep it going, how would this work? To me it sounds like a SUMIF equation which I'm currently using to tally up my different Schedule C line items, but Excel would need to associate January 19, 2017 with being on Thursday and so on and so forth. So it'll look at the dates and tally up sales for each day of the week and make an average of that but again, how do we bring in days of the week? Thanks!

[JohnTopley]

You will need something like

=SUMPRODUCT((WEEKDAY(B1:B3)=7)*(F1:F3))

This will SUM the data

B1:B3 are Dates

WEEKDAY returns the day of the week : in the example above 1=Sunday, 7=Saturday so the above is for Saturday

F1:F3 are your sales figures ....



This will Count the number of Saturdays

=SUMPRODUCT(--(WEEKDAY($B$1:$B$3)=7))

And average is

=SUMPRODUCT((WEEKDAY(B1:B3)=7)*(F1:F3))/SUMPRODUCT(--(WEEKDAY(B1:B3)=7)

Hope this helps

[Tony Valko]

Maybe something like this...

Data Range

	A	B	C	D
1	5/8/2003	11	Thu	AVG
2	7/4/2010	17	Sun	26.6
3	5/15/2002	38	Wed
4	10/10/2001	50	Wed
5	7/22/2012	59	Sun
6	5/14/2006	27	Sun
7	5/3/2005	32	Tue
8	9/26/2002	18	Thu
9	5/1/2003	42	Thu
10	1/7/2012	46	Sat
11	2/9/2009	31	Mon
12	4/12/2007	18	Thu
13	8/24/2004	5	Tue
14	12/8/2001	1	Sat
15	6/25/2009	44	Thu
16	------	------	------	------

This array formula** entered in D2:

=AVERAGE(IF(WEEKDAY(A1:A15,2)=4,B1:B15))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

4 represents Thursday. For other weekday averages just change the 4 to whatever day of the week you require:

1 = Mon
2 = Tue
3 = Wed
4 = Thu
5 = Fri
6 = Sat
7 = Sun

[savvv]

I tried John's method, and here's where we are.

=SUMPRODUCT((WEEKDAY(Jan!$A$3:$A$150)=1)*(Jan!$D$3:$D$150)+(WEEKDAY(Feb!$A$3:$A$150)=1)*(Feb!$D$3:$D$150))/SUMPRODUCT((--(WEEKDAY(Jan!$A$3:$A$150)=1))+(--(WEEKDAY(Feb!$A$3:$A$150)=1)))

I am putting these figures on the Year End tab, which is why you see the Jan! and Feb! here and there. So I'm pulling my dates from the A column in both January and February, and sales figures from the D column. I essentially took your formula and put a + sign in after the first bit so that February's numbers could be added to the first SUMPRODUCT, and this will continue to get longer once I add in the other months. Then, we divide by the same but making sure to add up all the A column dates for both months to get a grand total average of sales on each day of the week for the whole year. Only thing is it's showing an average of $8.56 for Saturday's, which is on number 7. Tony above is stating that Excel recognizes Monday as Day 1 of the week instead of Sunday. Either way, if 7 is Saturday or Sunday, looking at my sales data there's no way the average sales for Saturdays OR Sundays is only $8.56. What did I type in wrong?

PS...can I just say that it's no wonder companies like Quicken and Intuit exist. These formulas are just mind boggling. Makes me want to take an advanced class.

[Tony Valko]

=SUMPRODUCT((WEEKDAY(Jan!$A$3:$A$150)=1)*(Jan!$D$3:$D$150)+(WEEKDAY(Feb!$A$3:$A$150)=1)*(Feb!$D$3:$D$150))/SUMPRODUCT((--(WEEKDAY(Jan!$A$3:$A$150)=1))+(--(WEEKDAY(Feb!$A$3:$A$150)=1)))

Tony above is stating that Excel recognizes Monday as Day 1 of the week instead of Sunday.

No...

WEEKDAY(Jan!$A$3:$A$150)=1

1 = Sunday