# Average of a range of numbers....

#### sumospim

##### New Member
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

### Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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
PQRS
2JanFebMar
3Monday6372
4Tuesday6372
5Wednesday8477
6Thursday8463
7Friday9639
8Saturday7260
9Sunday7760
Sheet1

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

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>

Last edited:
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>

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

Excel Workbook
PQRSTUVWXYZAAABAC
2JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDECTotal
3Monday14.5149.214.58.7512.819.2519.2513.214.2516.7512.413.904
4Tuesday15.7515.59.51410.758.251918.7514.81514.512.214.077
5Wednesday17151114.216.2512.7518.617.2516.513.213.751214.811
6Thursday16.814.2511141511.7517.6161714.615.513.514.846
7Friday19.213.759.5141510.7517.7515.6171515.515.7515
8Saturday18169.215.513.611.7518.7515.6151715.213.7514.827
9Sunday1813.759.212.7511.7512.618.7515.613.7515.7515.215.7514.308
FIRM & FORECAST JAN 24-7

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:

Code:
``````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

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

Replies
0
Views
87
Replies
1
Views
369
Replies
5
Views
359
Replies
1
Views
238
Replies
4
Views
337

1,196,048
Messages
6,013,096
Members
441,747
Latest member
darkman77

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back