Formula that adds up WEEKLY, MONTH & YTD data from my table

[Skullypots]

Good Afternoon and Happy New Year to all Users & Mods.

I appreciate this is a tall task, but I need help with calculating, WEEKLY, MONTH and YEAR to date figures from my data table.

The data table is 365 columns across, from 1st Jan through to 31st December, and the scored for each of the 6 teams are input daily on a Monday through Friday.

I would like to track the totals and averages in a mini table below the data table.

These would be :


Totals for this week so far (The Active Week)

The average figure for the week so far.

----

Totals for last week so far (The Previous Week)

The average figure for that week.

----


Totals for this month so far (The Active Month)

The average figure for the month so far, based on how many work days have gone by.

----

Totals for last month (The previous Month)

The average figure for the month, based on how many work days in the month.

----

Year to Date totals

Average figures for the Year based on the number of work days so far

Average figures for the Year based on the number of work weeks so far

Average figures for the Year based on the number of worked months so far

----

I have input the figures manually (pink squares), looking for an adaptive formula that can scope the entire year, but just show the data requested.

Can you help?

Points to note :

Very rarely does anything get done on a Sat or Sun, we have to have columns for their figures if and when it happens, but we do not count this as an official work day. Any figures over the weekend, in the totals table, are added to Mondays. So 5 jobs on Saturday, and 10 jobs on Monday, would be 15 for the week, with the weekly average also been 15, 15 divided by 1 work day, not 2 work days etc.

I do have a count of the number of bank holidays COMPLETED so far, this is in A2. If a day is a BH we annotate it with a BH in row 1. These do NOT count as Workdays

Hope you can help.

Many thanks,

Rich

[josephteh]

In the YTD column, how do you determine the number of weeks and months? In your example, it's week 3, but you count the number of weeks as 2.

If today's date is 01-Feb, will you count the number of months as 1 or 2?

[josephteh]

Try these formulas:

Please Login or Register  to view this content.

[Skullypots]

Hi Joseph,

Thank you very much for looking at my work, and taking time to comment.

Good Question.

In the example, I was trying to be clever. Technically the 15th Jan is in the 3rd week.
What I thought would be more realistic is if I actually tried to calculate how many actual work weeks there were, so I could compare the averages with the weekly averages.
I was using a formula that counted the number of WORK days from the start of the year to the 15th, which is 11, and then took one away for the bank holiday.
This left 10 days, which was equates to 2 weeks. As the days went on, the figure would increase so on Thursday 16th the weekly would be 2.14.

Maybe I was trying to be too clever here, but I am happy to go with 3 if that makes it easier. After all this would be the 3rd week, so I am perfectly happy with that.

Considerering your second question, this has highlighted to me that I was trying to be too smart above.

If the date is Feb 1st then it would be Month 2. (Having thought about it, it makes sense to use the actual Months and weeks, so your suggestion above is better.)
If I used my logic that I started off with, the Month would equate to 1 month plus 1 day out of 30, and I can see how complicated that would get.

At the end of the day, I want it to be easy to read and understand, so yes, in answer to your question it would be Week 3 and Month 2.

Thank you very much for your help.

Cheers

Rich

[Skullypots]

THANK YOU !

This has saved me from further sleepless nights. I can't thank you enough for the help you have given me in responding with the completed spreadsheet.

I am working through your formula's to understand and educate myself for the future, so this is very much appreciated.

Cheers

Rich

[Skullypots]

Joseph - I'm curious, you use "<>S*" in your COUNTIF formula as the final criteria, what does this do ?

[josephteh]

Based on 3 weeks and 1 month:
Average per WEEK: =IF(N14=0,0,N14/WEEKNUM($B$11,16))
Average per MONTH: =IF(N14=0,0,N14/MONTH($B$11))

[josephteh]

"<>S*" to exclude Sat and Sun. "*" is a wild card character.

[josephteh]

You could use "<>S??" also. "*" indicates any number of characters (can be 1, 2, 3 or more), while "?" is a single character.

[Skullypots]

Thank you so much for that clear explanation, many thanks Joseph

[josephteh]

You are welcome.