Budget Payroll Increase

[goss]

Hi all,

I would like to test a planned week for payroll increase to begin and include the rate of increase in my determination of total wages for a given employee

Here is my formula at the moment:
=IF(INDEX($BL$26:$DL$26,1,MATCH($G231,$BL$26:$DL$26))>BL$26,IF(J231>40,ROUND(((40*$E231)+((J231-40)*($E231*1.5))),2),ROUND((J231*$E231),2)),IF(J231>40,ROUND(((40*($E231*(1+$F231)))+((J231-40)*(($E231*(1+$F231))*1.5))),2),ROUND((J231*($E231*(1+$F231))),2)))

Where:
$BL$26:$DL$26 = Week 1-53
$G231 = Week of planned increase
J231 = hrs worked week 1
E231 = Current rate
F231 = Planned rate of increase


It works, but it seems overly complicated. Is there an easier way?

 

[Roger Govier]

Hi

You could simplify it to

=ROUND(IF(INDEX($BL$26:$DL$26,1,MATCH($G231,$BL$26:$DL$26))>BL$26,
(1+$F231),1)*(J231*$E231)+(MAX(0,J231-40)*$E231*1.5),2)

 

[Roger Govier]

Hi

Actually it can be simplified further, and a little explanation of the logic.

You are only wanting to know if the payroll week number is greater than the week of increase.

=COLUMN(A1) will return a value of 1 and when copied across will increment to column(B1) and return 2 etc.

So the Only IF test is whether the week number is greater than the week number in BL$26
If it is, then use the multiplier of (1+ your % increase in $F231) or else multiply by 1

The payment is the same no matter what the multiplier is.
The employee gets base pay for all hours worked, plus in addition he gets 50% more for the hours above 40
So the payment is simply
=(J231*$E231)+(MAX(0,J231-40)*$E231*1.5)

Multiply that by 1 or 1.nn dependent upon week no
=IF(COLUMN(A1)>BL$26,(1+$F231),1)

then wrap the whole thing in a Round statement to give

=ROUND(IF(COLUMN(A1)>BL$26,(1+$F231),1)*(J231*$E231)+(MAX(0,J231-40)*$E231*1.5),2)

 

[goss]

Thanks Roger,

I'll give that a go tonight.
I'll need to digest it as I am not sure of the purpose of Column(A1)

 

[Roger Govier]

Hi Goss

Before you do try it - my bad!!!
I was paying your employees 2.5 times normal rate for their overtime!!!

the pay calculation of course should be
(J231*$E231)+(MAX(0,J231-40)*$E231*0.5)
as the employee has already been paid the 1.0 x rate as part of the first part of the equation.

The whole formula should be
=ROUND(IF(COLUMN(A1)>BL$26,(1+$F231),1)
*(J231*$E231)+(MAX(0,J231-40)*$E231*0.5),2)

The COLUMN() function
In any cell on a spreadsheet type = COLUMN() and it will return the column number of the cell in which you type it.
for example enter in E5 =COLUMN() (the row number is unimportant)
and you will see it return a value of 5 as E is the fifth column
Drag it across the page and you will see the value increase, as each successive column number is calculated

Now enter =COLUMN(A1) in E6 an you will see the value of 1 as that is the column number of column A
Drag it across the page and you will see the formula change to COLUMN(B1 with a value of 2, column(C1) with a value of 3 etc.

So, by placing COLUMN(A1) in our formula in the cell which represents week 1 of the year then we have the week number incrementing, without having to carry out an INDEX, MATCH function