Formula help , countifs, conditional formatting

[mariearcus]

This is a long shot but I'm hoping someone can help and that the calculation I'm trying to make can run in excel.

In brief, I work in HR and we need to create a new way to calculate holiday pay using extracted data (into excel) from our payroll system.

Holiday pay for 200 of our employees needs to be worked out by adding up the last 12 weeks or normal pay and dividing by 12. The issue is there are exceptions such as when the employee takes a day holiday, sick or attends college then we have to exclude that week and go back to week 13 et c....

So far I've just added conditional formatting to highlight which weeks should be included in the average 12 weeks calculation. My issue is how to create a formula to add up JUST the 12 weeks which should be included?

The spreadsheet is setup like this...

Column A - employee name
B - basic pay £
C - price work £
D - holiday pay £
E - sick pay £
F - college pay£
G - include/exclude

The spreadsheet includes pay data going back 20 weeks, but could be more or less depending on how we extract the data.

Each week I will need to download the data from our weekly payroll system and add to the spreadsheet so whatever formula I use needs to work like this.

 

[jtakw]

If you choose Dropbox, use the Personal version, not business, it's completely free for personal use. https://www.dropbox.com/

 

[mariearcus]

Does this work... ?

https://drive.google.com/file/d/0B4XQGrRga4kwdncwWEF4bi16Q2c/view?usp=sharing

 

[AngelEG]

Thanks for your continued effort to post the data, I am sorry this has been so dramatic, I'm not a big believer that seeking help should be so complicated.

I got the data you posted above and have separated in the a table, so the next questions are, what do the columns mean and what do you need to do with the data... sorry, still a little fuzzy to comprehend your payroll process.

from the data you posted this is what I gather, the exclude I imagine is for the weeks that don't count, then what needs to be done again?

thanks



Sheet1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

emp name

week

year

WnameB

exclude

WnameB

include

WnameB

include

WnameB

include

WnameB

exclude

WnameB

include

WnameB

include

WnameB

include

WnameB

include

WnameB

include

WnameB

include

WnameB

include

WnameB

include

WnameB

exclude

WnameB

exclude

WnameB

include

WnameB

include

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:73px;"><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;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]19[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]402.92[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]402.92[/TD]
[TD="align: right"]100.73[/TD]
[TD="align: right"]8.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]109.23[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]553.58[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]553.58[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]21[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]541.14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]541.14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]22[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]553.58[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]553.58[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]23[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]435.4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]435.4[/TD]
[TD="align: right"]105.74[/TD]
[TD="align: right"]8.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]114.24[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]24[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]541.14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]541.14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]25[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]528.7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]528.7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]26[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]553.58[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]553.58[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]27[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]528.7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]528.7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: right"]28[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]541.14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]541.14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="align: right"]29[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]566.02[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]566.02[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]528.7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]528.7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="align: right"]31[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]528.7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]528.7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="align: right"]32[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]528.7[/TD]
[TD="align: right"]42.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]571.2[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]

[TD="align: right"]33[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]422.96[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]422.96[/TD]
[TD="align: right"]105.74[/TD]
[TD="align: right"]8.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]114.24[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]

[TD="align: right"]34[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]541.14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]541.14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]

[TD="align: right"]35[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]547.36[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]547.36[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</tbody>

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

 

[AngelEG]

never mind, I see you file...

 

[jtakw]

Does this work... ?

https://drive.google.com/file/d/0B4XQGrRga4kwdncwWEF4bi16Q2c/view?usp=sharing

Yes, that's great.
Now, what happens if there are more or there are less than 12 weeks shown to be included for an employee?

 

[mariearcus]

Yes, that's great.
Now, what happens if there are more or there are less than 12 weeks shown to be included for an employee?

I have pasted more than 12 weeks data as the problem is when an employee takes a day off on holiday/sick or at college then we cannot include that week in the 12 week average. Therefore we would need to go back a further week or weeks to get an average 12 weeks of 'normal' work... I think this is where the formula could prove tricky. The 12 week reference period is the 12 weeks preceding the week they want to take as holiday (unless of the aforementioned scenario).

Basically I need to create a formula to add up 12 weeks of 'normal' pay each week (so it needs to understand to exclude those week where the employee is sick/training/on holiday). Then I can just divide that figure by 12. It's adding up the correct 12 weeks which I'm struggling with.

Also, each week I will be downloading another week of payroll info, so will need to run this report weekly.

Apologies if I'm not explaining this well !

https://www.nibusinessinfo.co.uk/content/calculating-and-paying-holiday-pay

 

[jtakw]

I asked because your table shows:

WnameB, 13 weeks, include
WnameA, 10 weeks, include
WnameC, 12 weeks, include

Should the formula be always dividing by 12, assuming you'll make adjustments weekly?

Here's what I have so far:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1			Include in 12 week average holiday pay						Exclude from 12 week average holiday pay
2	Employee	Pd. Yr	Basic Hours (pd. amount)	Price Work (pd. amount)	Time+Half (pd. amount)	Double Time (pd. amount)	Bonus (pd. amount)	Total 'Normal' Pay to be included	Holiday Pay (pd. amount)	Holiday Pay Hours (input amount)	College Hours (pd. amount)	Co.Sick Pay (pd. amount)	SSP (pd. amount)	Total - these weeks to be excluded	Include or Exclude in Holiday Pay Average	Holiday Pay (weekly rate) 12 week average of 'Normal' pay
3	WnameB	19 2015	402.92	0	0	0	0	402.92	100.73	8.5	0	0	0	109.23	exclude	$587.79
4	WnameB	20 2015	553.58	0	0	0	0	553.58	0	0	0	0	0	0	include
5	WnameB	21 2015	541.14	0	0	0	0	541.14	0	0	0	0	0	0	include
6	WnameB	22 2015	553.58	0	0	0	0	553.58	0	0	0	0	0	0	include
7	WnameB	23 2015	435.4	0	0	0	0	435.4	105.74	8.5	0	0	0	114.24	exclude
8	WnameB	24 2015	541.14	0	0	0	0	541.14	0	0	0	0	0	0	include
9	WnameB	25 2015	528.7	0	0	0	0	528.7	0	0	0	0	0	0	include
10	WnameB	26 2015	553.58	0	0	0	0	553.58	0	0	0	0	0	0	include
11	WnameB	27 2015	528.7	0	0	0	0	528.7	0	0	0	0	0	0	include
12	WnameB	28 2015	541.14	0	0	0	0	541.14	0	0	0	0	0	0	include
13	WnameB	29 2015	566.02	0	0	0	0	566.02	0	0	0	0	0	0	include
14	WnameB	30 2015	528.7	0	0	0	0	528.7	0	0	0	0	0	0	include
15	WnameB	31 2015	528.7	0	0	0	0	528.7	0	0	0	0	0	0	include
16	WnameB	32 2015	0	0	0	0	0	0	528.7	42.5	0	0	0	571.2	exclude
17	WnameB	33 2015	422.96	0	0	0	0	422.96	105.74	8.5	0	0	0	114.24	exclude
18	WnameB	34 2015	541.14	0	0	0	0	541.14	0	0	0	0	0	0	include
19	WnameB	35 2015	547.36	0	0	0	0	547.36	0	0	0	0	0	0	include
20	WnameA	19 2015	0	0	0	0	0	0	0	0	0	512.13	0	512.13	exclude	$627.88
21	WnameA	20 2015	0	0	0	0	0	0	0	0	0	512.13	0	512.13	exclude
22	WnameA	21 2015	0	0	0	0	0	0	0	0	0	512.13	0	512.13	exclude
23	WnameA	22 2015	51.21	0	0	0	0	51.21	0	0	0	409.7	17.69	427.39	exclude
24	WnameA	23 2015	0	0	0	0	0	0	102.43	8.5	0	0	70.76	181.69	exclude
25	WnameA	24 2015	0	850	0	0	0	850	0	0	0	0	0	0	include
26	WnameA	25 2015	0	800	0	0	0	800	0	0	0	0	0	0	include
27	WnameA	26 2015	0	817	0	0	0	817	0	0	0	0	0	0	include
28	WnameA	27 2015	0	900	0	0	0	900	102.43	8.5	0	0	0	110.93	exclude
29	WnameA	28 2015	0	731	0	0	0	731	0	0	0	0	0	0	include
30	WnameA	29 2015	0	600	0	0	0	600	0	0	0	0	0	0	include
31	WnameA	30 2015	0	348	0	0	0	348	204.85	17	0	0	0	221.85	exclude
32	WnameA	31 2015	0	607	0	0	0	607	0	0	0	0	0	0	include
33	WnameA	32 2015	0	689	0	0	0	689	0	0	0	0	0	0	include
34	WnameA	33 2015	0	839	0	0	0	839	0	0	0	0	0	0	include
35	WnameA	34 2015	0	851.5	0	0	0	851.5	0	0	0	0	0	0	include
36	WnameA	35 2015	0	750	0	0	0	750	0	0	0	0	0	0	include
37	WnameC	19 2015	276.25	0	0	0	0	276.25	0	0	0	0	0	0	include	$309.90
38	WnameC	20 2015	318.75	0	0	0	0	318.75	0	0	0	0	0	0	include
39	WnameC	21 2015	63.75	0	0	0	0	63.75	255	34	0	0	0	289	exclude
40	WnameC	22 2015	255	0	0	0	0	255	0	0	0	0	0	0	include
41	WnameC	23 2015	255	0	0	0	0	255	63.75	8.5	0	0	0	72.25	exclude
42	WnameC	24 2015	318.75	0	0	0	0	318.75	0	0	0	0	0	0	include
43	WnameC	25 2015	318.75	0	0	0	0	318.75	0	0	0	0	0	0	include
44	WnameC	26 2015	318.75	0	0	0	0	318.75	0	0	0	0	0	0	include
45	WnameC	27 2015	318.75	0	0	0	0	318.75	0	0	0	0	0	0	include
46	WnameC	28 2015	318.75	0	0	0	0	318.75	0	0	0	0	0	0	include
47	WnameC	29 2015	318.75	0	0	0	0	318.75	0	0	0	0	0	0	include
48	WnameC	30 2015	318.75	0	0	0	0	318.75	0	0	0	0	0	0	include
49	WnameC	31 2015	318.75	0	0	0	0	318.75	0	0	0	0	0	0	include
50	WnameC	32 2015	318.75	0	0	0	0	318.75	0	0	0	0	0	0	include
Sheet1

Cell Formulas
Range	Formula
P3	=SUMIFS(H$3:H$50,A$3:A$50,A3,O$3:O$50,"include")/12
P20	=SUMIFS(H$3:H$50,A$3:A$50,A20,O$3:O$50,"include")/12
P37	=SUMIFS(H$3:H$50,A$3:A$50,A37,O$3:O$50,"include")/12

For some reason, I could only copy your table upto Pd. Yr 32 2015...

 

[mariearcus]

Thank you

I've pasted that in but I think perhaps it needs alteration?

For example, adding up the weeks of pay to be included for holiday taken in week 35, so 12 weeks normal pay upto and including week 34 - means we use the data in H5, H6, H8-H15 and H17, H18. This adds up to £6,375.50 then dividing by 12 gives an answer of £531.29. I'd expect that amount to the result in cell P19.

Does this make sense?

Thanks so much for your help so far

 

[jtakw]

But H17 says exclude?

 

[AngelEG]

Ok, from your previous post, H17 is excluded so it shouldn't count right?

this is what I have, I added one additional column for calculation:

Sheet1

[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: #cacaca"]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD]Employee[/TD]
[TD]Pd. Yr[/TD]
[TD]Basic Hours
(pd. amount)[/TD]
[TD]Price Work
(pd. amount)[/TD]
[TD]Time+Half
(pd. amount)[/TD]
[TD]Double Time
(pd. amount)[/TD]
[TD]Bonus
(pd. amount)[/TD]
[TD]Total 'Normal' Pay to be included[/TD]
[TD]Holiday Pay
(pd. amount)[/TD]
[TD]Holiday Pay Hours
(input amount)[/TD]
[TD]College Hours
(pd. amount)[/TD]
[TD]Co.Sick Pay
(pd. amount)[/TD]
[TD]SSP
(pd. amount)[/TD]
[TD]Total - these weeks to be excluded[/TD]
[TD="align: center"]Include or Exclude in Holiday Pay Average[/TD]
[TD="align: center"]Count[/TD]
[TD="align: center"]Holiday Pay (weekly rate)

12 week average of 'Normal' pay[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD]WnameB[/TD]
[TD]19 2015[/TD]
[TD="align: right"]402.92[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]402.92[/TD]
[TD="align: right"]100.73[/TD]
[TD="align: right"]8.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]109.23[/TD]
[TD="align: center"]exclude[/TD]
[TD="align: center"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD]WnameB[/TD]
[TD]20 2015[/TD]
[TD="align: right"]553.58[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]553.58[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]include[/TD]
[TD="align: center"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD]WnameB[/TD]
[TD]21 2015[/TD]
[TD="align: right"]541.14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]541.14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]include[/TD]
[TD="align: center"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD]WnameB[/TD]
[TD]22 2015[/TD]
[TD="align: right"]553.58[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]553.58[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]include[/TD]
[TD="align: center"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD]WnameB[/TD]
[TD]23 2015[/TD]
[TD="align: right"]435.4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]435.4[/TD]
[TD="align: right"]105.74[/TD]
[TD="align: right"]8.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]114.24[/TD]
[TD="align: center"]exclude[/TD]
[TD="align: center"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD]WnameB[/TD]
[TD]24 2015[/TD]
[TD="align: right"]541.14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]541.14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]include[/TD]
[TD="align: center"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD]WnameB[/TD]
[TD]25 2015[/TD]
[TD="align: right"]528.7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]528.7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]include[/TD]
[TD="align: center"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD]WnameB[/TD]
[TD]26 2015[/TD]
[TD="align: right"]553.58[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]553.58[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]include[/TD]
[TD="align: center"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD]WnameB[/TD]
[TD]27 2015[/TD]
[TD="align: right"]528.7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]528.7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]include[/TD]
[TD="align: center"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD]WnameB[/TD]
[TD]28 2015[/TD]
[TD="align: right"]541.14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]541.14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]include[/TD]
[TD="align: center"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD]WnameB[/TD]
[TD]29 2015[/TD]
[TD="align: right"]566.02[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]566.02[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]include[/TD]
[TD="align: center"]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD]WnameB[/TD]
[TD]30 2015[/TD]
[TD="align: right"]528.7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]528.7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]include[/TD]
[TD="align: center"]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD]WnameB[/TD]
[TD]31 2015[/TD]
[TD="align: right"]528.7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]528.7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]include[/TD]
[TD="align: center"]11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD]WnameB[/TD]
[TD]32 2015[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]528.7[/TD]
[TD="align: right"]42.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]571.2[/TD]
[TD="align: center"]exclude[/TD]
[TD="align: center"]11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD]WnameB[/TD]
[TD]33 2015[/TD]
[TD="align: right"]422.96[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]422.96[/TD]
[TD="align: right"]105.74[/TD]
[TD="align: right"]8.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]114.24[/TD]
[TD="align: center"]exclude[/TD]
[TD="align: center"]11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD]WnameB[/TD]
[TD]34 2015[/TD]
[TD="align: right"]541.14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]541.14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]include[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]542.1766667[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD]WnameB[/TD]
[TD]35 2015[/TD]
[TD="align: right"]547.36[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]547.36[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]include[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]542.5753846[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]20[/TD]
[TD]WnameA[/TD]
[TD]19 2015[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]512.13[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]512.13[/TD]
[TD="align: center"]exclude[/TD]
[TD="align: center"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]21[/TD]
[TD]WnameA[/TD]
[TD]20 2015[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]512.13[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]512.13[/TD]
[TD="align: center"]exclude[/TD]
[TD="align: center"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]22[/TD]
[TD]WnameA[/TD]
[TD]21 2015[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]512.13[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]512.13[/TD]
[TD="align: center"]exclude[/TD]
[TD="align: center"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]23[/TD]
[TD]WnameA[/TD]
[TD]22 2015[/TD]
[TD="align: right"]51.21[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]51.21[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]409.7[/TD]
[TD="align: right"]17.69[/TD]
[TD="align: right"]427.39[/TD]
[TD="align: center"]exclude[/TD]
[TD="align: center"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]24[/TD]
[TD]WnameA[/TD]
[TD]23 2015[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]102.43[/TD]
[TD="align: right"]8.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]70.76[/TD]
[TD="align: right"]181.69[/TD]
[TD="align: center"]exclude[/TD]
[TD="align: center"]0[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Formula on P3 is =COUNTIFS($A$3:A3,A3,$O$3:O3,"include")
Formula on Q3 is =IF(P3<13,"",AVERAGEIF($O$3:O3,"include",$H$3:H3))

just copy down the formula, I believe this is what you're looking for?

here's the file is you wish to download -> https://drive.google.com/file/d/0B1t6R5UwL0-2d1NqRGNhMm0zTFE/view?usp=sharing

hope we're getting close.

Angel

Thank you

I've pasted that in but I think perhaps it needs alteration?

For example, adding up the weeks of pay to be included for holiday taken in week 35, so 12 weeks normal pay upto and including week 34 - means we use the data in H5, H6, H8-H15 and H17, H18. This adds up to £6,375.50 then dividing by 12 gives an answer of £531.29. I'd expect that amount to the result in cell P19.

Does this make sense?

Thanks so much for your help so far