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

 

[markmzz]

For year, you can try this:

Sum - in AC3 - 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),$B$2:$M$32))

Or

Average - in AC3 - 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),$B$2:$M$32))

Markmzz