Trying to make a formula to figure out what total hours in the month will be, based on what I already know

[kxellis26]

I need a formula that gives me an assumption of what the total hours will be at the end of the month – here is a breakdown of what I have an what I need.

• I have the dates, days of the week and total hours worked for each day
• Column G is the dates – G3:G33
• Column H is the day of the week – H3:H33
• Column K is the hours worked – K3:K33
• The only data that I have is what has already been worked – lets assume that I have data from the 1st through the 11th – now I want to know what the assumption is for the rest of the month, knowing what was working on the previous days of the week
• The assumption should say – I know what was worked on the first 2 Mondays and since I know there are 2 more, the assumption is that the average of those first two Mondays should apply to the next 2 Mondays – the same thing for the rest of the days of the week
• This will give us an assumption of what the total should be for the rest of the month and give a breakdown of that on cell K34
• I know that the data will be off if there are zero hours in a day
• I do not want to have to change this formula each day – it needs to be a running total, based on what we know

Date	Day	English T1
11/1	Fri	24.10
11/2	Sat	0.00
11/3	Sun	0.00
11/4	Mon	24.20
11/5	Tue	24.03
11/6	Wed	24.29
11/7	Thu	24.07
11/8	Fri	24.48
11/9	Sat	0.00
11/10	Sun	0.00
11/11	Mon	24.32
11/12	Tue	24.61
11/13	Wed	23.08
11/14	Thu	16.06
11/15	Fri	0.00
11/16	Sat	0.00
11/17	Sun	0.00
11/18	Mon	0.00
11/19	Tue	0.00
11/20	Wed	0.00
11/21	Thu	0.00
11/22	Fri	0.00
11/23	Sat	0.00
11/24	Sun	0.00
11/25	Mon	0.00
11/26	Tue	0.00
11/27	Wed	0.00
11/28	Thu	0.00
11/29	Fri	0.00
11/30	Sat	0.00
Total	233.24

 

[Sufiyan97]

Try

Book2
	A	B	C
1	Date	Day	English T1
2	1-Nov	Fri	24.1
3	2-Nov	Sat	0
4	3-Nov	Sun	0
5	4-Nov	Mon	24.2
6	5-Nov	Tue	24.03
7	6-Nov	Wed	24.29
8	7-Nov	Thu	24.07
9	8-Nov	Fri	24.48
10	9-Nov	Sat	0
11	10-Nov	Sun	0
12	11-Nov	Mon	24.32
13	12-Nov	Tue	24.61
14	13-Nov	Wed	23.08
15	14-Nov	Thu	16.06
16	15-Nov	Fri	24.29
17	16-Nov	Sat	0.00
18	17-Nov	Sun	0.00
19	18-Nov	Mon	24.26
20	19-Nov	Tue	24.32
21	20-Nov	Wed	23.69
22	21-Nov	Thu	20.07
23	22-Nov	Fri	24.29
24	23-Nov	Sat	0.00
25	24-Nov	Sun	0.00
26	25-Nov	Mon	24.26
27	26-Nov	Tue	24.32
28	27-Nov	Wed	23.69
29	28-Nov	Thu	20.07
30	29-Nov	Fri	24.29
31	30-Nov	Sat	0.00
Sheet1

Cell Formulas
Range	Formula
C16:C31	C16	=AVERAGEIF($B$2:B15,B16,$C$2:C15)