Average of a range of numbers....

[sumospim]

This is probably a very simple query for most but here goes...


Hi all, I have a range of figures (demand) that covers a whole year (2014) Link as follows https://app.box.com/s/j3h93ofib20yk22n38ln

The issue I have is that I want to try and get to a point where I can have an "average" figure for each day of the week in January ( and February etc.), as each figure in January at present is different.

I have added a second tab (requirement)on my attached spreadsheet that hopefully will help.

The requirement is where i want to get to in that in cells B2 to B25, i would like the average of all Mondays throughout the year from tab Firm and Forecast. Cells C2 to C25 i would like the average of all Tuesdays and so on.

Now as i say ideally for the whole year but with the Mondays figure being very varied (ranging from 8-14), i'm not sure whether an average is the best way forward. Even having 12 seperate months, ie tab 2 (requirement) for each month may not be the way.

This is where the low, core and high comes in where i may be able to group certain months where the demand figures are the same.


So i may have the following:

• One tab showing an average over the year
• 12 tabs showing the averages for each month
• 3 tabs showing groupings of similar months

Any help greatly apprecited.

Simon

 

[hiker95]

Cross posted here:
Average of a range of numbers....

A message to forum cross posters, please read the following link:
Excelguru Help Site - A message to forum cross posters

 

[dispelthemyth]

Now assuming you put the year in the table somewhere, such as A1 (where i put it)

you table being

Sheet1

*	A	B	C	D	E	F	G	H	I	J	K	L	M
1	2014	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	1	7	7	7	7	7	7	7	7	7	7	7	7
3	2	7	7	7	7	7	7	7	7	7	7	7	7
4	3	13	13	13	13	13	13	13	13	13	13	13	13
5	4	13	13	13	13	13	13	13	13	13	13	13	13
6	5	13	13	13	13	13	13	13	13	13	13	13	13
7	6	13	13	13	13	13	13	13	13	13	13	13	13
8	7	13	13	13	13	13	13	13	13	13	13	13	13
9	8	12	12	12	12	12	12	12	12	12	12	12	12
10	9	12	12	12	12	12	12	12	12	12	12	12	12
11	10	12	12	12	12	12	12	12	12	12	12	12	12
12	11	12	12	12	12	12	12	12	12	12	12	12	12
13	12	12	12	12	12	12	12	12	12	12	12	12	12

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup>

To get the averages by day for each month you can try the below formula
<code>
=SUM(--(WEEKDAY(TEXT($A$2:$A$32&"-"&B$1&"-"&$A$1,"DD/MM/YYYY")+0,2)=VLOOKUP($P3,{"Monday",1;"Tuesday",2;"Wednesday",3;"Thursday",4;"Friday",5;"Saturday",6;"Sunday",7},2,0))*B$2:B$32)
</code>

i put a table to the right of your table, months in Q2: AB2 and days of the week in P3:P9
in Q3 enter
<code>
=SUM(--(WEEKDAY(TEXT($A$2:$A$32&"-"&B$1&"-"&$A$1,"DD/MM/YYYY")+0,2)=VLOOKUP($P3,{"Monday",1;"Tuesday",2;"Wednesday",3;"Thursday",4;"Friday",5;"Saturday",6;"Sunday",7},2,0))*B$2:B$32)
</code>
Press Ctrl + Shift + Enter to confirm as its an array

Excel Workbook
	P	Q	R	S
2		Jan	Feb	Mar
3	Monday	63	72
4	Tuesday	63	72
5	Wednesday	84	77
6	Thursday	84	63
7	Friday	96	39
8	Saturday	72	60
9	Sunday	77	60
Sheet1

 

[sumospim]

Many thanks! I will give this a go...

 

[dispelthemyth]

Sorry reread and saw its averages you wanted, the below should work, again put in Q3 and drag down and across after pressing Ctrl + Shift + Enter

<code>
=SUM(--(IFERROR(WEEKDAY(TEXT($A$2:$A$32&"-"&B$1&"-"&$A$1,"DD/MM/YYYY")+0,2),0)=VLOOKUP($P3,{"Monday",1;"Tuesday",2;"Wednesday",3;"Thursday",4;"Friday",5;"Saturday",6;"Sunday",7},2,0))*B$2:B$32)/SUM(--(IFERROR(WEEKDAY(TEXT($A$2:$A$32&"-"&B$1&"-"&$A$1,"DD/MM/YYYY")+0,2),0)=VLOOKUP($P3,{"Monday",1;"Tuesday",2;"Wednesday",3;"Thursday",4;"Friday",5;"Saturday",6;"Sunday",7},2,0)))</code>



There are probably more efficient ways of doing this but it should work


The below should work on a full year average for each day
<Code>
=SUM(--(IFERROR(WEEKDAY(TEXT($A$2:$A$32&"-"&B$1:M$1&"-"&$A$1,"DD/MM/YYYY")+0,2),0)=VLOOKUP($P3,{"Monday",1;"Tuesday",2;"Wednesday",3;"Thursday",4;"Friday",5;"Saturday",6;"Sunday",7},2,0))*B$2:M$32)/SUM(--(IFERROR(WEEKDAY(TEXT($A$2:$A$32&"-"&B$1:M$1&"-"&$A$1,"DD/MM/YYYY")+0,2),0)=VLOOKUP($P3,{"Monday",1;"Tuesday",2;"Wednesday",3;"Thursday",4;"Friday",5;"Saturday",6;"Sunday",7},2,0)))</Code>

 

[sumospim]

HI dispelthemyth,

Tried the above but have value errors (Is it because of the Zero's?) I did Ctrl + Shift + Enter after entering the below in Q3.

The file is here, if you wouldn't mind taking a look https://app.box.com/s/vtlln1zw5rbckesohld2



<code>=SUM(--(IFERROR(WEEKDAY(TEXT($A$2:$A$32&"-"&B$1:M$1&"-"&$A$1,"DD/MM/YYYY")+0,2),0)=VLOOKUP($P3,{"Monday",1;"Tuesday",2;"Wednesday",3;"Thursday",4;"Friday",5;"Saturday",6;"Sunday",7},2,0))*B$2:M$32)/SUM(--(IFERROR(WEEKDAY(TEXT($A$2:$A$32&"-"&B$1:M$1&"-"&$A$1,"DD/MM/YYYY")+0,2),0)=VLOOKUP($P3,{"Monday",1;"Tuesday",2;"Wednesday",3;"Thursday",4;"Friday",5;"Saturday",6;"Sunday",7},2,0)))</code>

 

[dispelthemyth]

When i opened your file i noticed 2 things


1) You had the annual average formula in every month

You should create a new column for the annual average otherwise it will give you the same result every month

2)
In the monthly ones use

=SUM(--(IFERROR(WEEKDAY(TEXT($A$2:$A$32&"-"&B$1&"-"&$A$1,"DD/MM/YYYY")+0,2),0)=VLOOKUP($P3,{"Monday",1;"Tuesday",2;"Wednesday",3;"Thursday",4;"Friday",5;"Saturday",6;"Sunday",7},2,0))*B$2:B$32)/SUM(--(IFERROR(WEEKDAY(TEXT($A$2:$A$32&"-"&B$1&"-"&$A$1,"DD/MM/YYYY")+0,2),0)=VLOOKUP($P3,{"Monday",1;"Tuesday",2;"Wednesday",3;"Thursday",4;"Friday",5;"Saturday",6;"Sunday",7},2,0)))

3) Ctrl + Shift + Enter does not appear to have worked when you pressed it as i could not see the formula surrounded by two curly brackets {Your formula}
Try it again as when i did that the error went and the average appeared

 

[dispelthemyth]

Download link to your working file: https://www.dropbox.com/s/8kiusmoqcazxw5s/Excelforum.xlsx

Excel Workbook
	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
2		JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC	Total
3	Monday	14.5	14	9.2	14.5	8.75	12.8	19.25	19.25	13.2	14.25	16.75	12.4	13.904
4	Tuesday	15.75	15.5	9.5	14	10.75	8.25	19	18.75	14.8	15	14.5	12.2	14.077
5	Wednesday	17	15	11	14.2	16.25	12.75	18.6	17.25	16.5	13.2	13.75	12	14.811
6	Thursday	16.8	14.25	11	14	15	11.75	17.6	16	17	14.6	15.5	13.5	14.846
7	Friday	19.2	13.75	9.5	14	15	10.75	17.75	15.6	17	15	15.5	15.75	15
8	Saturday	18	16	9.2	15.5	13.6	11.75	18.75	15.6	15	17	15.2	13.75	14.827
9	Sunday	18	13.75	9.2	12.75	11.75	12.6	18.75	15.6	13.75	15.75	15.2	15.75	14.308
FIRM & FORECAST JAN 24-7

 

[markmzz]

HI dispelthemyth,

Tried the above but have value errors (Is it because of the Zero's?) I did Ctrl + Shift + Enter after entering the below in Q3.

The file is here, if you wouldn't mind taking a look https://app.box.com/s/vtlln1zw5rbckesohld2



<code>=SUM(--(IFERROR(WEEKDAY(TEXT($A$2:$A$32&"-"&B$1:M$1&"-"&$A$1,"DD/MM/YYYY")+0,2),0)=VLOOKUP($P3,{"Monday",1;"Tuesday",2;"Wednesday",3;"Thursday",4;"Friday",5;"Saturday",6;"Sunday",7},2,0))*B$2:M$32)/SUM(--(IFERROR(WEEKDAY(TEXT($A$2:$A$32&"-"&B$1:M$1&"-"&$A$1,"DD/MM/YYYY")+0,2),0)=VLOOKUP($P3,{"Monday",1;"Tuesday",2;"Wednesday",3;"Thursday",4;"Friday",5;"Saturday",6;"Sunday",7},2,0)))</code>

Another way:

For Sum - in Q3 - use Ctrl+Shift+Enter and not only Enter to enter the formula

=SUM(IF(IFERROR(WEEKDAY($A$2:$A$32&$B$1:$M$1&$A$1,2),0)=MATCH($P3,$P$3:$P$9,0),IF($B$1:$M$1=Q$2,$B$2:$M$32)))

And copy to the right and down.

Or

For Average - in Q3 - use Ctrl+Shift+Enter and not only Enter to enter the formula

=AVERAGE(IF(IFERROR(WEEKDAY($A$2:$A$32&$B$1:$M$1&$A$1,2),0)=MATCH($P3,$P$3:$P$9,0),IF($B$1:$M$1=Q$2,$B$2:$M$32)))

And copy to the right and down.

Markmzz

 

[sumospim]

Thanks so much, apologies for my lack of understanding but all understood now!