trending value projections without counting sundays

[jbrown021286]

TECH HOURS WORKSHEET 4.0.xlsx
	A					F	G			J	K			N	O			R	S	T	U	V	W	X	Y	Z	AA
1	Date					Average 2's Delta Per Tech	Total 2's deficit/ surplus			Average 3's Delta Per Tech	Dailey Total level 3 deficit/ surplus			Average 4's Delta Per Tech	Dailey Total 4's deficit/ surplus			Average Non-express 2's Delta Per Tech	Dailey Total Non-Express 2's deficit/ surplus	Total Express Available	Total Express Flagged	Average Express Delta Per Tech	Dailey Express deficit/ surplus	Dailey unapplyed labor from express	Total unapplied labor month to date	unapplyed labor trend projection	unapplyed conversion number
2	1-Feb					-2.84	-39.80			-0.23	-0.70			-6.10	-18.30			-0.18	-1.10	8	25.3	-4.84	-38.70	-$604.69	-$10,532.81		15.625
3	2-Feb					-3.46	-51.90			1.24	6.20			6.95	13.90			-0.92	-5.50	9	25.6	-5.16	-46.40	-$725.00
4	3-Feb					-3.41	-51.10			-0.55	-2.75			5.25	10.50			0.00	0.00	10	28.9	-5.11	-51.10	-$798.44
5	4-Feb																			0	0
6	5-Feb					-2.91	-29.10			1.00	2.00			-0.55	-1.10			-0.15	-0.45	7	27.35	-4.09	-28.65	-$447.66
7	6-Feb					-4.31	-56.00			3.80	11.40			5.27	15.80			-2.15	-8.60	9	24.6	-5.27	-47.40	-$740.63
8	7-Feb					-3.46	-45.00			-2.27	-6.80			-0.60	-1.80			0.35	1.40	9	25.6	-5.16	-46.40	-$725.00
9	8-Feb					-3.14	-43.90			1.25	5.00			-3.15	-6.30			-0.67	-4.00	8	24.1	-4.99	-39.90	-$623.44
10	9-Feb					-3.22	-45.10			3.90	15.60			2.40	4.80			-1.47	-8.80	8	27.7	-4.54	-36.30	-$567.19
11	10-Feb					-1.87	-26.20			-1.20	-3.60			2.77	8.30			0.48	2.90	8	34.9	-3.64	-29.10	-$454.69
12	11-Feb																			0	0
13	12-Feb					-3.46	-45.00			-0.78	-3.10			-3.30	-6.60			-2.73	-16.40	7	27.4	-4.09	-28.60	-$446.88
14	13-Feb					-3.08	-33.85			-0.47	-1.40			-0.15	-0.30			-2.53	-10.10	7	32.25	-3.39	-23.75	-$371.09
15	14-Feb					-2.23	-26.80			0.10	0.40			-0.75	-1.50			1.80	9.00	7	20.2	-5.11	-35.80	-$559.38	-$10,532.81
16	15-Feb					-3.08	-40.10			1.44	5.75			-4.10	-8.20			-0.50	-3.00	7	18.9	-5.30	-37.10	-$579.69
17	16-Feb					-2.49	-34.80			-0.74	-3.70			6.87	20.60			0.96	4.80	9	32.4	-4.40	-39.60	-$618.75
18	17-Feb					-3.03	-45.50			-0.15	-0.60			4.10	8.20			1.30	6.50	10	28	-5.20	-52.00	-$812.50
19	18-Feb																			0	0
20	19-Feb					-3.25	-39.00			2.91	11.65			3.85	7.70			-0.57	-2.30	8	27.3	-4.59	-36.70	-$573.44
21	20-Feb					-2.95	-32.50			1.17	3.50			-1.70	-5.10			-2.54	-12.70	6	28.2	-3.30	-19.80	-$309.38
22	21-Feb					-2.73	-35.50			-0.40	-0.80			2.93	8.80			0.26	1.30	8	27.2	-4.60	-36.80	-$575.00
23	22-Feb																			0	0
24	23-Feb																			0	0
25	24-Feb																			0	0
26	25-Feb																			0	0
27	26-Feb																			0	0
28	27-Feb																			0	0
29	28-Feb																			0	0
30	29-Feb																			0	0
31	1-Mar																			#N/A	#N/A
32	2-Mar																			#N/A	#N/A
33	Average					-2.98	-40.06			1.23	4.78			1.69	3.80			-0.95	-4.57			-4.16	-31.10	-$585.16	-$10,532.81
34						Average 2's Delta Per Tech	Total 2's deficit/ surplus			Average 3's Delta Per Tech	Dailey Total level 3 deficit/ surplus			Average 4's Delta Per Tech	Dailey Total 4's deficit/ surplus			Average Non-express 2's Delta Per Tech	Dailey Total Non-Express 2's deficit/ surplus	Total Express Available	Total Express Flagged	Average Express Delta Per Tech	Dailey Express deficit/ surplus	Average Dailey unapplied labor	Total unapplied labor month to date
Delta Breakbown

Cell Formulas
Range	Formula
F2:F32,J2:J32,N2:N32,R2:R32,V2:V32	F2	=IFERROR(E2/D2-8,"")
G2:G32,K2:K32,O2:O32,S2:S32,W2:W32	G2	=IFERROR(F2*D2,"")
T2:T32	T2	=INDEX('Calculated Data'!$C$46:$AG$46,MATCH(A2,'Calculated Data'!$C$31:$AG$31,0))
U2:U32	U2	=INDEX('Calculated Data'!$C$47:$AG$47,MATCH(A2,'Calculated Data'!$C$31:$AG$31,0))
X2:X32	X2	=IFERROR(W2*$AA$2,"")
Y2	Y2	=SUM(X2:X32:X32)
Y15	Y15	=SUM(X2:X32:X32)
F33,V33:W33,R33:S33,N33:O33,J33:K33	F33	=AVERAGE(F20:F32)
G33,X33	G33	=AVERAGE(G2:G32)
Y33	Y33	=SUM(X2:X32:X32)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
V2:Z33	Expression	=OR(V2=0,V2="")	text	NO
V2:Z33	Cell Value	<-1.5	text	NO
V2:Z33	Cell Value	<0	text	NO
V2:Z33	Cell Value	>1.5	text	NO
V2:Z33	Cell Value	>0	text	NO
R2:S33	Expression	=OR(R2=0,R2="")	text	NO
R2:S33	Cell Value	<-1.5	text	NO
R2:S33	Cell Value	<0	text	NO
R2:S33	Cell Value	>1.5	text	NO
R2:S33	Cell Value	>0	text	NO
N2:O33	Expression	=OR(N2=0,N2="")	text	NO
N2:O33	Cell Value	<-1.5	text	NO
N2:O33	Cell Value	<0	text	NO
N2:O33	Cell Value	>1.5	text	NO
N2:O33	Cell Value	>0	text	NO
J2:K33	Expression	=OR(J2=0,J2="")	text	NO
J2:K33	Cell Value	<-1.5	text	NO
J2:K33	Cell Value	<0	text	NO
J2:K33	Cell Value	>1.5	text	NO
J2:K33	Cell Value	>0	text	NO
F2:G33	Expression	=OR(F2=0,F2="")	text	NO
F2:G33	Cell Value	<-1.5	text	NO
F2:G33	Cell Value	<0	text	NO
F2:G33	Cell Value	>1.5	text	NO
F2:G33	Cell Value	>0	text	NO
G2:G32,K2:K32,O2:O32,S2:S32	Expression	=OR(G2=0,G2="")	text	NO
G2:G32,K2:K32,O2:O32,S2:S32	Cell Value	<-1.5	text	NO
G2:G32,K2:K32,O2:O32,S2:S32	Cell Value	<0	text	NO
G2:G32,K2:K32,O2:O32,S2:S32	Cell Value	>1.5	text	NO
G2:G32,K2:K32,O2:O32,S2:S32	Cell Value	>0	text	NO

i am wanting to have cell z33 have a month end projection of the values in column x. the math is simple the average in x33 * the number of working days in the month. the table shown rips the dates from another source which is why you see a few days of the next month. is there a way to only count the days in the current month and also not count any Sundays to then multiply by x33?

 

[KRice]

I don't follow your worksheet, but if the "current month" is the one that always appears in cell A2, then this will return the number of working days in the month, ignoring Sundays. You could also ignore holidays if desired, but you would need to add the 4th argument to the NETWORKDAYS.INTL function.

=NETWORKDAYS.INTL($A$2,EOMONTH($A$2,0),"0000001")

 

[KRice]

I should have clarified...cell A2 would represent the start date, so the formula assumes that A2 will be the 1st of the month of interest. If that is not the case, then the first argument in the NETWORKDAYS.INTL function will need to be revised...just post back if that is the case.