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 A...
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 + 1
Hope 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.