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.

 

[mariearcus]

But H17 says exclude?

Apologies! You are quite correct.

It should be (to work out holiday pay for week 35 holiday). Adding up the cells H4-H6, H8-H15 and H18... total of £6,506.12, divided by 12 is £542.17.

Perhaps do we have to add in the formula some element of including column B for the period and working back 12 weeks from that? Is that possible?

 

[Nathan_Hale]

Hi mariearcus,

I believe I am following your request. You have already determined the weeks that can be included based on sick/holiday time. Then next step would be to determine the 12 most recent weeks by employee. This is complicated as you have stated that you have more than 12 includable options. There are always a couple different ways to do things in Excel, but I sorted your data by the week/year column descending (so the most recent week/year will be at the top of your dataset). Once sorted, I used the following formula to find the includable week number for each employee (most recent week would be 1, next is 2, and so on), while excluding the records that were not listed as 'include'.

I put employee name in Column A and Include/Exclude information in Column O.
Formula in P2: =IF(O2<>"include","",COUNTIFS($A$2:A2,A2,$O$2:O2,O2))

We now have enough information to solve your analysis. From here, you can use a formula, or you could summarize with a pivot table. Regardless of method you are now able to filter/criteria on only WeekNumber's between 1 and 12 - excluding blanks and any numbers above 12.

If you are not comfortable with doing a pivot table, a formula won't be easier. In that case you could filter on week number between 1 and 12 and copy that data to a new tab. That way you would have only the data you want, and you could SUMIF() on just the employee name.

NH


Example:
[TABLE="width: 463"]
<tbody>[TR]
[TD]Employee_Name
[/TD]
[TD]Week/Year
[/TD]
[TD]IncludeStatus
[/TD]
[TD]WeekNumber
[/TD]
[/TR]
[TR]
[TD]WnameA
[/TD]
[TD]35 2015
[/TD]
[TD]include
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]WnameB
[/TD]
[TD]35 2015
[/TD]
[TD]include
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]WnameC
[/TD]
[TD]35 2015
[/TD]
[TD]include
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]WnameD
[/TD]
[TD]35 2015
[/TD]
[TD]include
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]WnameA
[/TD]
[TD]34 2015
[/TD]
[TD]include
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]WnameB
[/TD]
[TD]34 2015
[/TD]
[TD]include
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]WnameC
[/TD]
[TD]34 2015
[/TD]
[TD]include
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]WnameD
[/TD]
[TD]34 2015
[/TD]
[TD]include
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]WnameA
[/TD]
[TD]33 2015
[/TD]
[TD]include
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]WnameB
[/TD]
[TD]33 2015
[/TD]
[TD]exclude
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WnameC
[/TD]
[TD]33 2015
[/TD]
[TD]include
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]WnameD
[/TD]
[TD]33 2015
[/TD]
[TD]include
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]WnameA
[/TD]
[TD]32 2015
[/TD]
[TD]include
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]WnameB
[/TD]
[TD]32 2015
[/TD]
[TD]exclude
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WnameC
[/TD]
[TD]32 2015
[/TD]
[TD]include
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]WnameD
[/TD]
[TD]32 2015
[/TD]
[TD]include
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]WnameA
[/TD]
[TD]31 2015
[/TD]
[TD]include
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]WnameB
[/TD]
[TD]31 2015
[/TD]
[TD]include
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]WnameC
[/TD]
[TD]31 2015
[/TD]
[TD]include
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]WnameD
[/TD]
[TD]31 2015
[/TD]
[TD]include
[/TD]
[TD]5
[/TD]
[/TR]
</tbody>[/TABLE]

 

[AngelEG]

Hi mariearcus,

the file with the solution is here -> https://drive.google.com/file/d/0B1t6R5UwL0-2d1NqRGNhMm0zTFE/view?usp=sharing

this works as you describe below.

let me know if you have any additional questions.


Angel

Apologies! You are quite correct.

It should be (to work out holiday pay for week 35 holiday). Adding up the cells H4-H6, H8-H15 and H18... total of £6,506.12, divided by 12 is £542.17.

Perhaps do we have to add in the formula some element of including column B for the period and working back 12 weeks from that? Is that possible?

 

[mariearcus]

Angel - that's amazing! Thank you so much, I can't believe that was so quick, just goes to show how much I need to learn!

I'm intrigued to find out what the formulas mean - in as basic language as you can explain! The column you added for 'count' in column P is really useful.

Will I be ok to copy and paste these formulas in as I add the data each week or will it cause any issues?

Many thanks again

 

[mariearcus]

Angel - just looking at the result in cell Q53 and not sure it's right. It should be adding up the cells H42-H53 which is £3825... then dividing by 12 should be £318.75 but it says £506.92?

 

[AngelEG]

You are right! I made an awful mistake, it was counting all the way to the top, but we need to restart with every employee change... sorry about that.

I've fixed it, it's using an indirect referencing (the formulas will look a little awkward but they work), and I had to add yet a new column.

I'll reply later today with the explanation of what the formulas are doing (I'm in the middle of something right now).

here's the updated file -> https://drive.google.com/open?id=0B1t6R5UwL0-2d1NqRGNhMm0zTFE

Let me know if everything is square now.

thanks,

Angel

Angel - just looking at the result in cell Q53 and not sure it's right. It should be adding up the cells H42-H53 which is £3825... then dividing by 12 should be £318.75 but it says £506.92?

 

[AngelEG]

Hi mariearcus,

Ok, here's the explanation, there are several thinks happening here:

1) Let's start with formula on P3 =COUNTIFS($A$3:A3,A3,$O$3:O3,"include")

This is counting on a range from $A$3 (the beginning of the list) to the current cell (A3 in the first line but will increase as the formula is copied) and is increasing every time two things happen 1) the name is the same (that's the ,A3, this changes once you copy the formula down) 2) counts the "include" weeks beginning from the top of the list $O$3 to the current cell (initially O3 but increases as the formula is copied). So this creates a count of the included weeks; this identifies when we need to start calculating the 12 week holiday pay.

2) Formula on Q3 =IF(A3<>A2,ROW(H3),Q2)

This is only checking when the name of the employee has changed, so when ever the name changes it will return the row number of when it changed, this is needed for the calculation in the "R" column.

3) The last formula I will reference row 18, as it's where it begins to do something, formula on R18 =IF(P18<12,"",AVERAGEIFS(INDIRECT("H"&Q18&":"&CELL("address",H18),TRUE),INDIRECT("O"&Q18&":"&CELL("address",O18),TRUE),"include",INDIRECT("P"&Q18&":"&CELL("address",P18),TRUE),">"&P18-12))

This one reads like a tongue twister, but it's not as complex as it looks, the first part is the simple IF(P18<12, this is only checking if we have enough weeks to calculate, if we don't it simply returns empty "".

The second part uses a method called INDIRECTreference, Excel translates the formula into a Range reference, the translation on cell R18 would read: AVERAGEIF(H3:H18,O3:O18,"include",P3:P18,">"&P18-12), please note that the 3 in the formula comes from the Q column, that's the reason that we need it.
Anyway, what this is doing is establishing that we are going to average the salaries on H3:H18 with the following rules 1) only average the values in column H where range O3:018 reads "include"; 2) only average the values in column H where the week value in range P3:P18 is > 0 (that's the P18-12, this will change on R19 and will read P19-12 which translate to > 1, hence averaging salaries from week 2 through 12 to maintain the 12 week average).

To finalize the explanation, column Q changes when employee name changes, so the evaluation range (referenced by the INDIRECT formula) will shift dynamically for each employee to only average their own week range.

Hope above is clear and I didn't confuse you more.

Cheers,
Angel

 

[AngelEG]

Hi mariearcus,

Please disregard my previous post; I thought about it a second time and found a cleaner solution.

I got rid of the tongue twisting formula and the extra column, here's the formula for column Q =IF(P18<12,"",AVERAGEIFS($H$3:H18,$O$3:O18,"include",$P$3:P18,">"&P18-12,$A$3:A18,A18))

So, the simpler explanation is =IF(P18<12,"",AVERAGEIFS($H$3:H18,$O$3:O18,"include",$P$3:P18,">"&P18-12,$A$3:A18,A18))

The first part, if we don't have 12 weeks, do nothing and return empty "". Second part, average from top of table $H$3 to current row H18, following condition 1: range $O$3 to current row must read "include"; condition 2: range $P$3 to current row is > P18-12 (P18=12 so 12-12=0 so > 0, for next row P19=13 this changes to > 1, so we keep averaging only 12 weeks at a time); condition 3: for range $A$3 to current range only use the name were the current row is at (this is the part that excludes other employees and will average only the current employee named in the current row).

You can find the solution file here https://drive.google.com/file/d/0B1t6R5UwL0-2d1NqRGNhMm0zTFE/view?usp=sharing

Cheers,

Angel

 

[Peter_SSs]

mariearcus

You may well want to keep those helper columns, but in case you are interested, I believe that you can do your calculations without them as follows.

The formula in O3 is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Excel Workbook
	A	H	N	O
2	Employee	Total 'Normal' Pay to be included	Total - these weeks to be excluded	Holiday Pay (weekly rate) 12 week average of 'Normal' pay
3	WnameB	402.92	109.23
4	WnameB	553.58	0
5	WnameB	541.14	0
6	WnameB	553.58	0
7	WnameB	435.4	114.24
8	WnameB	541.14	0
9	WnameB	528.7	0
10	WnameB	553.58	0
11	WnameB	528.7	0
12	WnameB	541.14	0
13	WnameB	566.02	0
14	WnameB	528.7	0
15	WnameB	528.7	0
16	WnameB	0	571.2
17	WnameB	422.96	114.24
18	WnameB	541.14	0	542.18
19	WnameB	547.36	0	541.66
20	WnameA	0	512.13
21	WnameA	0	512.13
22	WnameA	0	512.13
23	WnameA	51.21	427.39
24	WnameA	0	181.69
25	WnameA	850	0
26	WnameA	800	0
27	WnameA	817	0
28	WnameA	900	110.93
29	WnameA	731	0
30	WnameA	600	0
31	WnameA	348	221.85
32	WnameA	607	0
33	WnameA	689	0
34	WnameA	839	0
35	WnameA	851.5	0
36	WnameA	750	0
37	WnameC	276.25	0
38	WnameC	318.75	0
39	WnameC	63.75	289
40	WnameC	255	0
41	WnameC	255	72.25
42	WnameC	318.75	0
43	WnameC	318.75	0
44	WnameC	318.75	0
45	WnameC	318.75	0
46	WnameC	318.75	0
47	WnameC	318.75	0
48	WnameC	318.75	0
49	WnameC	318.75	0
50	WnameC	318.75	0	309.90
51	WnameC	318.75	0	313.44
52	WnameC	318.75	0	313.44
53	WnameC	318.75	0	318.75
54	WnameD	410.5	0
55	WnameD	450.19	0
56	WnameD	322.14	0
57	WnameD	312.99	0
58	WnameD	114.77	191.25
59	WnameD	64.7	63.75
60	WnameD	0	139.58
61	WnameD	227.14	0
62	WnameD	514.84	0
63	WnameD	413.64	65.45
64	WnameD	355.81	65.45
65	WnameD	460.16	0
66	WnameD	515.13	0
67	WnameD	443.42	0
68	WnameD	368.39	0
69	WnameD	341.57	0
70	WnameD	338.41	0	392.07
Sheet1 (2)

 

[mariearcus]

Thank you for your help - apologies I've been off line for the weekend and yesterday (Christmas stuff!) - so just looking at this now