dynamic formula help - supply multiple variables and process row by row

B

BrettJH

New Member
Joined
Jul 20, 2023
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi,

Hoping someone can help or point me in the right direction!
I have a complex formula which essentially takes 7 input values and performs a calculation.

The goal of the formula is to calcuclate full or partial salaries for a given month based on multiple criteria. I would like to provide the critera as variables, so far I've named the variables:
  • EmpID - the Employee ID from Column C is passed to the CompForecast lambda function to Filter the Compensation table and determine the Comp amount for the given month
  • cMnth - is the date of the current month
  • StartDate - the Employee Starting date Column I is used to determine when the Employee started and determines Comp for the days
  • EndDate - Column J same concept as StartDate, but calculates when the Employment ended
  • Leave - indicates employee had a type of unpaid leave which should be excluded
  • The LeaveStart and LeaveEnd dates work similar to Start/EndDates as above and determine the Comp for the period
I have the formula working in a single cell passing arguments to a condensed version that uses 3 lambda functions, CompForecast, MonthlySalary and LeaveCalculator to produce the correct results, Columns AA:AC
As the number of Employees will change I would like to make it dynamic to spill down for all employees and across for all dates in the current FY but I don't understand how to pass multiple ranges as named variables and have the formula process row by row.

A Simplified version of the source data looks like, with expected output in AA:AC:

Cell X11 shows the full expanded formula but includes the function CompForecast
Cell Y11 contains the condensed formula

Dynamic Query.xlsx
BCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
3These are the correct values - I would like to make the formula dynamic to spill down and if possible also across
4
5Names/IDs vary over time - data is imported via PowerQuery01/05/2023
6
7Variables:EmpIDStartDateEndDateLeaveLeaveStartLeaveEndcMnth
8
9Employee NameEmployee No.Yet to StartDeptTitleActivityEmailStart DateEnd DateLoSHoursP statusP StartP EndEmp ContCo contLeave typeLeave StartLeave EndComp DateAnn SalaryFull Monthly formulaCondensed Monthly formula31-May-2330-Jun-2331-Jul-23
10
11Joe Bloggs10307-Oct-19-SAB05-May-2315-Jul-2301-Jan-2329000311.84311.84311.840.011,247.32
12John Doe10030-Mar-20-UPL10-Jul-2331-Aug-2301-Jan-23310002,583.332,583.33750.01
13Jane Smith10129-Aug-1715-Jul-23--01-Jul-23460003,333.333,333.331,916.67
14Richard Roe102Yet to start01-Jun-23---01-Jan-23400000.003,333.333,333.33
Formula Help
Cell Formulas
RangeFormula
X11X11=LET(EmpID, $C11,Empstart,$I11, EmpEnd, $J11,Leave,$R11,LeaveStart,$S11,LeaveEnd,$T11,cMnth, AA$9, Salary, CompForecast(EmpID,cMnth), x, IF(Leave<>"", IF(AND(EOMONTH(LeaveStart,0)=cMnth,EOMONTH(LeaveEnd,0)=cMnth), Salary * SUM(DAY(LeaveStart)-1,DAY(cMnth)-DAY(LeaveEnd))/DAY(cMnth)+0.01, IF(AND(EOMONTH(LeaveStart,0)<=cMnth,EOMONTH(LeaveEnd,0)>=cMnth), IF(EOMONTH(LeaveStart, 0) = EOMONTH(cMnth, 0), Salary * (DAY(LeaveStart) - 1) / DAY(cMnth) + 0.01, IF(EOMONTH(LeaveEnd, 0) = cMnth, Salary * (DAY(cMnth) - DAY(LeaveEnd)) / DAY(cMnth) + 0.01, 0.01) ), IF(AND(EOMONTH(Empstart,0)=cMnth, EOMONTH(EmpEnd,0)=cMnth), Salary * (DAY(EmpEnd)-(DAY(Empstart)-1))/DAY(cMnth), IF(EOMONTH(Empstart,0)=cMnth, Salary * ((DAY(Empstart)-1-DAY(EOMONTH(Empstart,0)))/-DAY(EOMONTH(Empstart,0))), IF(EOMONTH(EmpEnd,0)=cMnth, Salary * (DAY(EmpEnd)/(cMnth-(EOMONTH(cMnth,-1)+1))), IF(AND(Empstart<cMnth,OR(EmpEnd=0,EOMONTH(EmpEnd,0)>=cMnth)), Salary, 0) ) ) ) ) ), IF(AND(EOMONTH(Empstart,0)=cMnth, EOMONTH(EmpEnd,0)=cMnth), Salary * (DAY(EmpEnd)-(DAY(Empstart)-1))/DAY(cMnth), IF(EOMONTH(Empstart,0)=cMnth, Salary * ((DAY(Empstart)-1-DAY(EOMONTH(Empstart,0)))/-DAY(EOMONTH(Empstart,0))), IF(EOMONTH(EmpEnd,0)=cMnth, Salary * (DAY(EmpEnd)/(cMnth-(EOMONTH(cMnth,-1)+1))), IF(AND(Empstart<cMnth,OR(EmpEnd=0,EOMONTH(EmpEnd,0)>=cMnth)), Salary, 0) ) ) ) ),x)
Y11Y11=LET(EmpID, $C11,Empstart,$I11, EmpEnd, $J11,Leave,$R11,LeaveStart,$S11,LeaveEnd,$T11,cMnth, AA$9, Salary,CompForecast(EmpID,cMnth), Output, IF(Leave<>"", IF(AND(EOMONTH(LeaveStart,0)=cMnth,EOMONTH(LeaveEnd,0)=cMnth), Salary * SUM(DAY(LeaveStart)-1,DAY(cMnth)-DAY(LeaveEnd))/DAY(cMnth)+0.01, IF(AND(EOMONTH(LeaveStart,0)<=cMnth,EOMONTH(LeaveEnd,0)>=cMnth), LeaveCalculator(LeaveStart,LeaveEnd,cMnth,Salary), MonthlySalary(Empstart,EmpEnd,cMnth,Salary) ) ), MonthlySalary(Empstart,EmpEnd,cMnth,Salary) ),Output)
AA11:AC11,AA12:AB14AA11=LET(EmpID, $C11,Empstart,$I11, EmpEnd, $J11,Leave,$R11,LeaveStart,$S11,LeaveEnd,$T11,cMnth, AA$9, Salary,CompForecast(EmpID,cMnth), Output, IF(Leave<>"", IF(AND(EOMONTH(LeaveStart,0)=cMnth,EOMONTH(LeaveEnd,0)=cMnth), Salary * SUM(DAY(LeaveStart)-1,DAY(cMnth)-DAY(LeaveEnd))/DAY(cMnth)+0.01, IF(AND(EOMONTH(LeaveStart,0)<=cMnth,EOMONTH(LeaveEnd,0)>=cMnth), LeaveCalculator(LeaveStart,LeaveEnd,cMnth,Salary), MonthlySalary(Empstart,EmpEnd,cMnth,Salary) ) ), MonthlySalary(Empstart,EmpEnd,cMnth,Salary) ),Output)
AC12:AC14AC12=LET(EmpID, $C12,Empstart,$I12, EmpEnd, $J12,Leave,$R12,LeaveStart,$S12,LeaveEnd,$T12,cMnth, AC$9, Salary, CompForecast(EmpID,cMnth), x, IF(Leave<>"", IF(AND(EOMONTH(LeaveStart,0)=cMnth,EOMONTH(LeaveEnd,0)=cMnth), Salary * SUM(DAY(LeaveStart)-1,DAY(cMnth)-DAY(LeaveEnd))/DAY(cMnth)+0.01, IF(AND(EOMONTH(LeaveStart,0)<=cMnth,EOMONTH(LeaveEnd,0)>=cMnth), IF(EOMONTH(LeaveStart, 0) = EOMONTH(cMnth, 0), Salary * (DAY(LeaveStart) - 1) / DAY(cMnth) + 0.01, IF(EOMONTH(LeaveEnd, 0) = cMnth, Salary * (DAY(cMnth) - DAY(LeaveEnd)) / DAY(cMnth) + 0.01, 0.01) ), IF(AND(EOMONTH(Empstart,0)=cMnth, EOMONTH(EmpEnd,0)=cMnth), Salary * (DAY(EmpEnd)-(DAY(Empstart)-1))/DAY(cMnth), IF(EOMONTH(Empstart,0)=cMnth, Salary * ((DAY(Empstart)-1-DAY(EOMONTH(Empstart,0)))/-DAY(EOMONTH(Empstart,0))), IF(EOMONTH(EmpEnd,0)=cMnth, Salary * (DAY(EmpEnd)/(cMnth-(EOMONTH(cMnth,-1)+1))), IF(AND(Empstart<cMnth,OR(EmpEnd=0,EOMONTH(EmpEnd,0)>=cMnth)), Salary, 0) ) ) ) ) ), IF(AND(EOMONTH(Empstart,0)=cMnth, EOMONTH(EmpEnd,0)=cMnth), Salary * (DAY(EmpEnd)-(DAY(Empstart)-1))/DAY(cMnth), IF(EOMONTH(Empstart,0)=cMnth, Salary * ((DAY(Empstart)-1-DAY(EOMONTH(Empstart,0)))/-DAY(EOMONTH(Empstart,0))), IF(EOMONTH(EmpEnd,0)=cMnth, Salary * (DAY(EmpEnd)/(cMnth-(EOMONTH(cMnth,-1)+1))), IF(AND(Empstart<cMnth,OR(EmpEnd=0,EOMONTH(EmpEnd,0)>=cMnth)), Salary, 0) ) ) ) ),x)


the formulas for the named lambda functions are:

Dynamic Query.xlsx
BC
19Lambda Named Functions
20CompForecast=LAMBDA(EmpID,cMnth, FILTER(Comp[Rate],(Comp[ID]=EmpID)*(Comp[Date]=MAXIFS(Comp[Date],Comp[ID],EmpID,Comp[Date],"<="&cMnth)),0)/12)
21
22LeaveCalculator=LAMBDA(LeaveStart,LeaveEnd,cMnth,Salary,IF(EOMONTH(LeaveStart, 0) = EOMONTH(cMnth, 0), Salary * (DAY(LeaveStart) - 1) / DAY(cMnth) + 0.01, IF(EOMONTH(LeaveEnd, 0) = cMnth, Salary * (DAY(cMnth) - DAY(LeaveEnd)) / DAY(cMnth) + 0.01, 0.01)))
23
24MonthlySalary=LAMBDA(EmpStart,EmpEnd,cMnth,Salary,IF(AND(EOMONTH(EmpStart, 0) = cMnth, EOMONTH(EmpEnd, 0) = cMnth), Salary * (DAY(EmpEnd) - (DAY(EmpStart) - 1)) / DAY(cMnth), IF(EOMONTH(EmpStart, 0) = cMnth, Salary * ((DAY(EmpStart) - 1 - DAY(EOMONTH(EmpStart, 0))) / -DAY(EOMONTH(EmpStart, 0))), IF(EOMONTH(EmpEnd, 0) = cMnth, Salary * (DAY(EmpEnd) / (cMnth - (EOMONTH(cMnth, -1) + 1))), IF(AND(EmpStart < cMnth, OR(EmpEnd = 0, EOMONTH(EmpEnd, 0) >= cMnth)), Salary, 0)))))
Formula Help
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
You must log in or register to reply here.

Similar threads

D
  • Question Question
Graph/Chart with Multiple Dropdown
Replies
0
Views
449
davidmor
D
M
  • Solved
Need help adapting a formula
Replies
2
Views
624
mattbird
M
A
  • Question Question
Lookup Material Name Material Unit/Day Consumed Duration and accumulate and compact abstract to the destination
Replies
2
Views
87
abuharvey
A
M
  • Question Question
Copied sheet code to new file and lost some functionality....not sure how/why
Replies
5
Views
90
MeaclH
M
U
  • Solved
SumIf formula with multiple criteria from different tab
Replies
3
Views
489
Alex Blakenburg
A

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top