Forum Discussion

Brirack77's avatar
Brirack77
Copper Contributor
Jan 26, 2026

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

community
excel
Formulas and Functions
office 365

2 Replies

  • IlirU's avatar
    IlirU
    Iron Contributor
    Jan 26, 2026

    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 + 1

    In cell K2:

    =(C2:C7 * (G2:G7 - F2:F7 + 1)) / I2:I7 * J2:J7

    In cell H11:

    =G12:G19 - F12:F19 + 1

     

    Hope this helps.

    IlirU

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jan 26, 2026

    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.

Resources