Bank holidays pro rata for part timers

[stevembe]

I am trying to get my head around calculating bank holiday pro rata entitlement for 2016 but first I need to figure out how many bank holidays fall on the days that part timers work. In column P1:P3 I have the number of bank holidays by day so:

Mon 5
Tue 1
Fri 2

Then in columns H to L I have Mon – Fri and if the figure is greater than 0 that signifies a working day. In column G I want to sum up the bank holidays that would normally be a working day as follows:

No of B/H Mon TUE WED THUR FRI
8 8.10 8.10 8.10 0 8.10
8 6.00 6.00 6.00 6.00 6.00
6 5.00 5.00 5.00 5.00 0
8 5.00 5.00 5.00 5.00 5.00
3 0.00 6.40 6.40 6.40 6.40

Can anybody suggest a way to do this or suggest an alternative that would work.

Thanks for reading.

 

[stevembe]

Is there a way I can do this by combining IF, e.g. IF H1>0 then add P1 and IF I2>0 then add P2 and finally IF L2>0 then add P3. I am able to make a simple if statement but not sure how to combine them all into one formula.

 

[stevembe]

Is there a way I can do this by combining IF, e.g. IF H2>0 then add P1 and IF I2>0 then add P2 and finally IF L2>0 then add P3. I am able to make a simple if statement but not sure how to combine them all into one formula.

In the last post H1 should have read H2, apologies for the confusion, having a bad day. It might explain it better if it is displayed like this:

=IF(H2>0) add 5
=IF(I2>0) add 1
=IF(L2>0) add 2

So if H2, I2 and L2 are > 0 then cell totals 8. If only H2, I2 >0 then cell totals 6. If H2, L2 >0 then cell totals 7 and so on for any combination for the three cells. The aim is to do the whole calculation in a single cell.

 

[stevembe]

I appreciate the help I get on here but I am up against time, I don’t expect anybody to rush and help on my part, fully understand it is my problem. I have had to run with the following in 3 columns then sum in a fourth column. Not what I hoped for but it solves the problem.

=IF(H2>0, "5")*IF(H2=0,"0")
=IF(I2>0, "1")*IF(I2=0, "0")
=IF(L2>0, "2")*IF(L2=0, "0")

Thanks for taking the time to look.