averages of averages for a given day

[S.Peto]

Hi.

I have two columns of data.... column A = dates, column B = money

I have multiple entries for each date. I am trying to return an average of the average of each day for a long list.

I have this:

{=SUMPRODUCT((WEEKDAY($A$2:$A$100,2)=7)*($B$2:$B$100))*(1/COUNTIF(A2:A100,A2:A100))}

It needs to go through the list of dates and when it finds a sunday, add up and average THAT sunday, then find the next sunday and average it, then average the two Sundays together. I need it to work for the future, I add about 500 rows per month..

[FDibbins]

1 way would be yo add a helper, and test for sunday, then pull that value.

if that doesnt work, I suggest that you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[FDibbins]

1 way would be yo add a helper, and test for sunday, then pull that value.

if that doesnt work, I suggest that you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[S.Peto]

sunday 1/1/16 20
sunday 1/1/16 10
sunday 1/1/16 30
monday 1/2/16 5
....
....
sunday 1/8/16 30

outcome 25

[Tony Valko]

I don't know how you'd do this with a single formula.

You can get the individual Sunday averages in one step and then get the average of those averages.

Data Range

	A	B	C	D	E	F
1	Date	Weekday	Value	Sun AVG	------	Overall AVG
2	1/3/2016	Sun	20	37		35.55
3	1/3/2016	Sun	54
4	1/3/2016	Sun	37
5	1/4/2016	Mon	4
6	1/5/2016	Tue	76
7	1/10/2016	Sun	49	49
8	1/11/2016	Mon	72
9	1/17/2016	Sun	47	41.2
10	1/17/2016	Sun	26
11	1/17/2016	Sun	23
12	1/17/2016	Sun	71
13	1/17/2016	Sun	39
14	1/18/2016	Mon	90
15	1/24/2016	Sun	10	15
16	1/24/2016	Sun	20
17

This formula entered in D2 and copied down:

=IFERROR(IF(WEEKDAY(A2)=1,IF(MATCH(A2,A:A,0)=ROW(),AVERAGEIF(A:A,A2,C:C),""),""),"")

This formula entered in F2:

=AVERAGE(D:D)

[Tony Valko]

I don't know how you'd do this with a single formula.

You could do it in a single formula if, and it's a big if, the values to be averaged followed a set pattern.

For example, the values appear in groups of 2 every 6th row.

[JohnTopley]

It would be (more) accurate (and mathematically correct) to average Sundays-to date rather than average averages.:

sunday 1/1/16 20
sunday 1/1/16 10
sunday 1/1/16 30
monday 1/2/16 5
....
....
sunday 1/8/16 30

would give average of 90/4 = 22.5 rather than Average of averages (20+30)/2 =25

[S.Peto]

John, you got me thinking and you are correct, but I was confused. I don't want an average of an average. I need the average of the totals for each Sunday. So in my above example the outcome would be 45.

Look at the formula my first post... I think it's either correct or close to it, but I can't change it to go beyond 100.

[Tony Valko]

If you simply want the average for all Sundays...

Data Range

	A	B	C
1	Date	Value	Sun AVG
2	1/3/2016	20	36
3	1/3/2016	54
4	1/3/2016	37
5	1/4/2016	4
6	1/5/2016	76
7	1/10/2016	49
8	1/11/2016	72
9	1/17/2016	47
10	1/17/2016	26
11	1/17/2016	23
12	1/17/2016	71
13	1/17/2016	39
14	1/18/2016	90
15	1/24/2016	10
16	1/24/2016	20

This array formula** entered in C2:

=AVERAGE(IF(WEEKDAY(A2:A16)=1,B2:B16))

** 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.

[JohnTopley]

I need the average of the totals for each Sunday.

Using data from Tony's last example and changing his AVERAGEIF (post #5) to SUMIF, the Sunday totals will be 111, 49, 206 and 30.

=IFERROR(IF(WEEKDAY(A2)=1,IF(MATCH(A2,A:A,0)=ROW(),SUMIF(A:A,A2,B:B),""),""),"")

A moving average of these figures will be 111, 80, 122 and 99

111/1, 160/2, 366/3 and 396/4

Is this the required result?

[Tony Valko]

I need the average of the totals for each Sunday.

I think it's about time that you posted a SMALL sample file that shows us what result you expect.

[FDibbins]

As was requested in post #2 lol

[S.Peto]

Ok, here is a sample of data and the expected result. Now I need it to work for 1000s of rows down with a random amount of sunday entries for each date.

[JohnTopley]

Use the formula I gave in my example: it will give the average in your posted file.

See result in column D

[S.Peto]

Using data from Tony's last example and changing his AVERAGEIF (post #5) to SUMIF, the Sunday totals will be 111, 49, 206 and 30.

=IFERROR(IF(WEEKDAY(A2)=1,IF(MATCH(A2,A:A,0)=ROW(),SUMIF(A:A,A2,B:B),""),""),"")

A moving average of these figures will be 111, 80, 122 and 99

111/1, 160/2, 366/3 and 396/4

Is this the required result?

John, I want one formula to give me your cell E15 value. It needs to work projected out into blank cells as well.. like A10000 or something.

[Tony Valko]

Use the formula I gave in my example: it will give the average in your posted file.

See result in column D

Sorry for off-topic interjection:

Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Thanks again for all your hard work here!

[JohnTopley]

@Tony,
The formula and file with solution was provided in a previous post (which I could (should!) have referenced).

If the OP (or others ???) had looked at my previous reply, he would not have needed to post his last file.

[Tony Valko]

Fair enough.

[JohnTopley]

Sum ALL Sunday figures and divide by number of unique Sundays.

[JohnTopley]

Try

=SUMPRODUCT(($B2:$B16)*(WEEKDAY($A2:$A16)=1))/INT((WEEKDAY($A2-1)+$A16-$A2)/7)


B2:B16 Sales data

A2:A16 : dates in ascending date order

For Monday change 1 to 2, Tuesday=3 etc

[S.Peto]

please forgive me if I am missing something...

I still do not have a formula that works...

John, did you miss post #15? I replied to you. Also, =IFERROR(IF(WEEKDAY(A2)=1,IF(MATCH(A2,A:A,0)=ROW(),SUMIF(A:A,A2,B:B),""),""),"") is blank when i put it in my sheet.

[JohnTopley]

Try formula in post #20

[S.Peto]

Try formula in post #20

I am getting a #div/0 error when i put it into a cell in the sample i provided.

[JohnTopley]

You need to adjust the ranges:

=SUMPRODUCT(($B$2:$B$50)*(WEEKDAY($A$2:$A$50)=1))/INT((WEEKDAY($A2-1)+$A50-$A2)/7)

Set cell to format NUMBER

[S.Peto]

ok hold on, I changed your formula to extend to row 50 in my sample and it works... however, that formula gets screwed up when i change it to row 1000 for example... I need it to include new items I add and not get screwed up by blank cells not yet filled out

[S.Peto]

You need to adjust the ranges:

=SUMPRODUCT(($B$2:$B$50)*(WEEKDAY($A$2:$A$50)=1))/INT((WEEKDAY($A2-1)+$A50-$A2)/7)

Set cell to format NUMBER

yes, i changed it, but see above... I need it to go far into the future with an unknown amount of rows.

[JohnTopley]

Try ....


=SUMPRODUCT(($B$2:$B$1000)*(WEEKDAY($A$2:$A$1000)=1))/INT((WEEKDAY($A2-1)+LOOKUP(99999,A2:A1000)-$A2)/7)

[S.Peto]

Try ....


=SUMPRODUCT(($B$2:$B$1000)*(WEEKDAY($A$2:$A$1000)=1))/INT((WEEKDAY($A2-1)+LOOKUP(99999,A2:A1000)-$A2)/7)

I think you just nailed it. I changed the 1000s to 10000 and did it for each day of the week and it seems like its checking out.

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[S.Peto]

Something seems possibly wrong....

Using your formula I get the average of the totals for each sunday. If I go through my real set of data and find a missing sunday date (skipped because there are no entries) and I add the date and put in 0 value, it should bring the average down (1 more sunday to average against), but it does not... unless your formula is already accounting for ALL sundays on it's own regardless of if there is a value there or not...

[Phuocam]

Or try this ...

=SUM(IF((WEEKDAY(A2:A10000)=1)*(A2:A10000<>""),B2:B10000))
/SUM(IF((WEEKDAY(A2:A10000)=1)*(A2:A10000<>""),1/COUNTIF(A2:A10000,A2:A10000)))

Enter with Ctrl+Shift+Enter.

[Tony Valko]

Or try this ...

=SUM(IF((WEEKDAY(A2:A10000)=1)*(A2:A10000<>""),B2:B10000))
/SUM(IF((WEEKDAY(A2:A10000)=1)*(A2:A10000<>""),1/COUNTIF(A2:A10000,A2:A10000)))

Enter with Ctrl+Shift+Enter.

Better if you used dynamic ranges.

1/COUNTIF(A2:A10000,A2:A10000)