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.

 

[AngelEG]

Can you post some data and exemplify what functionality are you looking for depending on the conditions you describe? I don't have a clear picture of what calculations are needed.

for example, where is the week number in your spreadsheet described above? how do you need this to be skipped... not clear what needs to be done.

I can probably help if you add a little more detail.

Cheers,

Angel

 

[mariearcus]

Thanks Angel

If you could help that would be amazing. I will try and explain a bit better...

Week number is in column H.

We are required to work out holiday pay using the preceding 12 weeks "normal" pay. This means only weeks where employees receive basic pay or price work. If they take a days holiday or sick et c then we cannot include this in the 12 weeks. So we replace this week with a previous week where normal pay was given. To calculate holiday pay we will then add up those 12 normal weeks pay and divide by 12. E.g.

Employee books a week's holiday for week 36...
In weeks 21-30 they worked normal hours/price work
In week 30-33 they were off sick (so we need to exclude this)
In week 34-35 they worked normal week/pay

We need to add up the total pay for week 21-30 and 34-35 then divide by 12 to find out the holiday pay they should receive in week 36.

Does this help explain?

I will try and post some data, just on mobile at the minute

 

[jtakw]

Hi,

You missed the point, we need you to post a sample of your data in order to see how we may help.

Read here on how to post samples: http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2545970

 

[mariearcus]

Hi,

You missed the point, we need you to post a sample of your data in order to see how we may help.

Read here on how to post samples: http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2545970

Hi, I understand and will post some data but I'm only on my mobile so no access to excel. I will upload tomorrow

 

[mariearcus]

Hi

How do I post an attachment? I looked at the link but couldn't really understand. I've tried to paste the data below but I'm not sure this will work well at all !!

Any help would be much appreciated!

Thanks
M


Include in 12 week average holiday pay Exclude from 12 week average holiday pay
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"
WnameB 19 2015 402.92 0 0 0 0 402.92 100.73 8.5 0 0 0 109.23 exclude
WnameB 20 2015 553.58 0 0 0 0 553.58 0 0 0 0 0 0 include
WnameB 21 2015 541.14 0 0 0 0 541.14 0 0 0 0 0 0 include
WnameB 22 2015 553.58 0 0 0 0 553.58 0 0 0 0 0 0 include
WnameB 23 2015 435.4 0 0 0 0 435.4 105.74 8.5 0 0 0 114.24 exclude
WnameB 24 2015 541.14 0 0 0 0 541.14 0 0 0 0 0 0 include
WnameB 25 2015 528.7 0 0 0 0 528.7 0 0 0 0 0 0 include
WnameB 26 2015 553.58 0 0 0 0 553.58 0 0 0 0 0 0 include
WnameB 27 2015 528.7 0 0 0 0 528.7 0 0 0 0 0 0 include
WnameB 28 2015 541.14 0 0 0 0 541.14 0 0 0 0 0 0 include
WnameB 29 2015 566.02 0 0 0 0 566.02 0 0 0 0 0 0 include
WnameB 30 2015 528.7 0 0 0 0 528.7 0 0 0 0 0 0 include
WnameB 31 2015 528.7 0 0 0 0 528.7 0 0 0 0 0 0 include
WnameB 32 2015 0 0 0 0 0 0 528.7 42.5 0 0 0 571.2 exclude
WnameB 33 2015 422.96 0 0 0 0 422.96 105.74 8.5 0 0 0 114.24 exclude
WnameB 34 2015 541.14 0 0 0 0 541.14 0 0 0 0 0 0 include
WnameB 35 2015 547.36 0 0 0 0 547.36 0 0 0 0 0 0 include
WnameA 19 2015 0 0 0 0 0 0 0 0 0 512.13 0 512.13 exclude
WnameA 20 2015 0 0 0 0 0 0 0 0 0 512.13 0 512.13 exclude
WnameA 21 2015 0 0 0 0 0 0 0 0 0 512.13 0 512.13 exclude
WnameA 22 2015 51.21 0 0 0 0 51.21 0 0 0 409.7 17.69 427.39 exclude
WnameA 23 2015 0 0 0 0 0 0 102.43 8.5 0 0 70.76 181.69 exclude
WnameA 24 2015 0 850 0 0 0 850 0 0 0 0 0 0 include
WnameA 25 2015 0 800 0 0 0 800 0 0 0 0 0 0 include
WnameA 26 2015 0 817 0 0 0 817 0 0 0 0 0 0 include
WnameA 27 2015 0 900 0 0 0 900 102.43 8.5 0 0 0 110.93 exclude
WnameA 28 2015 0 731 0 0 0 731 0 0 0 0 0 0 include
WnameA 29 2015 0 600 0 0 0 600 0 0 0 0 0 0 include
WnameA 30 2015 0 348 0 0 0 348 204.85 17 0 0 0 221.85 exclude
WnameA 31 2015 0 607 0 0 0 607 0 0 0 0 0 0 include
WnameA 32 2015 0 689 0 0 0 689 0 0 0 0 0 0 include
WnameA 33 2015 0 839 0 0 0 839 0 0 0 0 0 0 include
WnameA 34 2015 0 851.5 0 0 0 851.5 0 0 0 0 0 0 include
WnameA 35 2015 0 750 0 0 0 750 0 0 0 0 0 0 include
WnameC 19 2015 276.25 0 0 0 0 276.25 0 0 0 0 0 0 include
WnameC 20 2015 318.75 0 0 0 0 318.75 0 0 0 0 0 0 include
WnameC 21 2015 63.75 0 0 0 0 63.75 255 34 0 0 0 289 exclude
WnameC 22 2015 255 0 0 0 0 255 0 0 0 0 0 0 include
WnameC 23 2015 255 0 0 0 0 255 63.75 8.5 0 0 0 72.25 exclude
WnameC 24 2015 318.75 0 0 0 0 318.75 0 0 0 0 0 0 include
WnameC 25 2015 318.75 0 0 0 0 318.75 0 0 0 0 0 0 include
WnameC 26 2015 318.75 0 0 0 0 318.75 0 0 0 0 0 0 include
WnameC 27 2015 318.75 0 0 0 0 318.75 0 0 0 0 0 0 include
WnameC 28 2015 318.75 0 0 0 0 318.75 0 0 0 0 0 0 include
WnameC 29 2015 318.75 0 0 0 0 318.75 0 0 0 0 0 0 include
WnameC 30 2015 318.75 0 0 0 0 318.75 0 0 0 0 0 0 include
WnameC 31 2015 318.75 0 0 0 0 318.75 0 0 0 0 0 0 include
WnameC 32 2015 318.75 0 0 0 0 318.75 0 0 0 0 0 0 include
WnameC 33 2015 318.75 0 0 0 0 318.75 0 0 0 0 0 0 include
WnameC 34 2015 318.75 0 0 0 0 318.75 0 0 0 0 0 0 include
WnameC 35 2015 318.75 0 0 0 0 318.75 0 0 0 0 0 0 include
WnameD 19 2015 276.25 70.87 63.38 0 0 410.5 0 0 0 0 0 0 include
WnameD 20 2015 368.88 81.31 0 0 0 450.19 0 0 0 0 0 0 include
WnameD 21 2015 276.25 45.89 0 0 0 322.14 0 0 0 0 0 0 include
WnameD 22 2015 276.25 36.74 0 0 0 312.99 0 0 0 0 0 0 include
WnameD 23 2015 99.13 15.64 0 0 0 114.77 165.75 25.5 0 0 0 191.25 exclude
WnameD 24 2015 55.25 9.45 0 0 0 64.7 55.25 8.5 0 0 0 63.75 exclude
WnameD 25 2015 0 0 0 0 0 0 0 0 0 0 139.58 139.58 exclude
WnameD 26 2015 165.75 37.01 24.38 0 0 227.14 0 0 0 0 0 0 include
WnameD 27 2015 446.88 67.96 0 0 0 514.84 0 0 0 0 0 0 include
WnameD 28 2015 227.8 63.56 75.38 46.9 0 413.64 56.95 8.5 0 0 0 65.45 exclude
WnameD 29 2015 227.8 70.22 57.79 0 0 355.81 56.95 8.5 0 0 0 65.45 exclude
WnameD 30 2015 284.75 86.63 75.38 13.4 0 460.16 0 0 0 0 0 0 include
WnameD 31 2015 284.75 101.4 75.38 53.6 0 515.13 0 0 0 0 0 0 include
WnameD 32 2015 284.75 63.19 75.38 20.1 0 443.42 0 0 0 0 0 0 include
WnameD 33 2015 284.75 58.51 25.13 0 0 368.39 0 0 0 0 0 0 include
WnameD 34 2015 284.75 56.82 0 0 0 341.57 0 0 0 0 0 0 include
WnameD 35 2015 284.75 53.66 0 0 0 338.41 0 0 0 0 0 0 include

 

[jtakw]

Hi,

What you show in the above post is hard to decipher, if you have trouble understanding how to upload a snapshot of your table from the link (did you read the part regarding MrExcel HTML Maker?), perhaps you can upload a sample of your worksheet to something like dropbox https://www.dropbox.com/business?ho...759062|1t1|c&gclid=CLedofLa08kCFQaRfgod25gKJA, or a file sharing host of your choice.

 

[Peter_SSs]

How do I post an attachment? I looked at the link but couldn't really understand.

If you are having trouble with that, perhaps this might help.

 

[mariearcus]

Thanks both for your suggestions - I'm at work and it looks like I don't have the right level of access to install or run anything like dropbox without having to contact IT. I will perhaps try on my own laptop when I'm home later/at the weekend. Really frustrating... I have the spreadsheet here, just cant share it with you!

 

[mariearcus]

Perhaps, could I email it to a main email address at all?