Hi

Im trying to calculate the redundancy pay for 200+ staff


The formula im struggle with which seems to round up some of the week entitlements is

=IF(E2<2,0,SUMPRODUCT(LOOKUP(ROW(INDIRECT("1:"&MIN(E2,20)))+C2-MIN(E2,20),{0,22,41;0.5,1,1.5})))


Header are: and its the redundancy entitlement that is is out by 0.5 of a week on some staff (not consistant)


Start Date Years of Service Annual Pay Weekly Pay Weekly Pay (Statutory Cap) Termination Date Redundancy Entitlement Redundancy Pay (Cap £16,140) Statutory Notice Period (Cap 12 weeks)