Forum Discussion
Formula help - daily pay vs supplement monthly payments
Hi all,
I have two sets of data for bonus calculations.
SET ONE is in multiple rows by employee, e.g. the annual pay from 1 Jan to 31 Mar. Then 1 Apr to 31 Dec - when their pay has changed from 1 Apr onwards. And then a count of the number of days per row. I use this count to calculate the annual bonus.
SET TWO is any annual supplement paid, by calendar month, that I need to split by days relevant to their pay in SET ONE and add to their salary for that period.
So how can i take the data in SET TWO, and split by the same time period in SET ONE - and cut the Supp Amount and add to the Supplement Row column, thus updating the Total Paid column? I have 2k employees to calculate
e.g.
Thanks
8 Replies
I'm new to this forum so forgive me if this isn't how I need to ask for help with a new issue that I'm having.
You seem very intelligent in your solution posts above on complex issues so here it is.
When I use any FX functions the formulas look bizarre (foreign) and then the correct result doesn't return (here is an example).
HelloBrirack77,
This is not a data structure issue. Excel can short circuit array evaluation when MIN and MAX logic is embedded directly inside SUMPRODUCT. When that happens one overlapping supplement row can be ignored.
The fix is to explicitly test for date overlap and then prorate by days. This method is deterministic and works for changing pay periods and calendar month supplements.
Corrected formula
=SUMPRODUCT((Supp!$A$2:$A$10000=$A2)(Supp!$D$2:$D$10000<=$G2)(Supp!$E$2:$E$10000>=$F2)(MIN($G2,Supp!$E$2:$E$10000)-MAX($F2,Supp!$D$2:$D$10000)+1)/Supp!$F$2:$F$10000Supp!$C$2:$C$10000)
Explanation
This forces an explicit date overlap test prevents Excel from skipping rows and correctly prorates supplements against any pay period.
Excel 365 LET version
=LET(Emp,$A2,PayFrom,$F2,PayTo,$G2,SuppEmp,Supp!$A$2:$A$10000,SuppFrom,Supp!$D$2:$D$10000,SuppTo,Supp!$E$2:$E$10000,SuppDays,Supp!$F$2:$F$10000,SuppAmt,Supp!$C$2:$C$10000,Overlap,(SuppEmp=Emp)(SuppFrom<=PayTo)(SuppTo>=PayFrom)(MIN(PayTo,SuppTo)-MAX(PayFrom,SuppFrom)+1),SUMPRODUCT(Overlap/SuppDaysSuppAmt))
For very large datasets Power Query avoids recalculation issues entirely.
Hi,
In cell D2:
=MAP(B2:B7, F2:F7, G2:G7, LAMBDA(a,b,c, SUM((B12:B19 = a) * C12:C19 * (F12:F19 >= b) * (G12:G19 <= c))))In cell H2:
=G2:G7 - F2:F7 + 1In cell K2:
=(C2:C7 * (G2:G7 - F2:F7 + 1)) / I2:I7 * J2:J7In cell H11:
=G12:G19 - F12:F19 + 1Hope this helps.
IlirU
thanks IlirU! This works to a point, but I have a % that it ignores one line. I wonder if my data is too complex where the SET ONE dates can be any from / to and the SET TWO dates are in whole calendar months
Hi @ Brirack77,
You will need to explain your problem more clearly so that we can understand it and then try to help you. So try to give us a sample of your file in such a way that it can be copied and then pasted into MS Excel, so provide your data (not sensitive data but fictitious data similar to your original data) and show the expected results and provide the necessary explanations as to how these results are obtained.
Hi Brirack77
A Power Query option in attached file with
- Sheet 'Data', 2 tables named SetOne & SetTwo
- Sheet 'Result' the output of the query (pic. cropped):
How To use:
- Enter your data in the SetOne & SetTwo tables (they can be on seperate sheets)
- Switch to sheet 'Result'
- Right-click somewhere in the green table > Refresh*
* Can auto-refresh every n minutes if necessary but I wouldn't recommend with 2K Emp.
Thanks Lorenzo, this does work for most employees but not all - it seems to skip one supplement line for certain employees - help!
Brirack77
OK but without actual data I don't see what I can do. Please provide a realistic - anonymized - sample of records where this fails. Thanks
EDIT: Take your time, I'll be off the next couple of days...
