I have sheets that autocalculate the P&L for each project. At the moment they're using a fixed salary but I'd like to change it to incorporate any future salary changes. The project duration is Sequenced from L1 based on a duration parameter in E2 using =EDATE($C$2, SEQUENCE(1, $E$2, 0))
Each cell in column L sequences through the duration, calculating the relevant values.
The formula for the salary costs refences the dataset below which is also automatically generated from data in another sheet using =FILTER(FILTER(AllStaffProjectAllocationTbl,AllStaffProjectAllocationTbl[Project Code Name]=B2), {1,0,0,1,1,1,0,0,0,1,0,0,0,1,0,0,0,0}) and can have a variable number of entries:
The current formua for calculating the salary costs in L7 (from the first image) that someone on here kindly helped me with is =MMULT(SEQUENCE(1,ROWS($A$18#),1,0),($L$1#>=OFFSET($A$18#,0,3,,1))*($L$1#<=OFFSET($A$18#,0,4,,1))*OFFSET($A$18#,0,5,,1)) and uses the column F (which I'm hoping to remove) from the second image .
I've got a new set of data in the table EmployeeSalaryTbl for the salaries here (I had to add in the made up end date of 31/12/9999 for now to ensure calculations work below, ideally this would be blank and I'd check for that in the below calculation):
I've begun modifying the MMult function in L7 to do the following:
- Sequence for the duration of the project
- Check if the employees from A18 onwards are on the project for the given month starting from L1
- If they are then find the salary that falls within the date range from the EmployeeSalaryTbl and add them all together for the month.
This is what I have so far but unfortunately it's giving me an error:
=MMULT(SEQUENCE(1,ROWS($A$18#),1,0),IF(AND(($L$1#>=OFFSET($A$18#,0,3,,1))*($L$1#<=OFFSET($A$18#,0,4,,1))),SUMIFS(EmployeeSalaryTbl[Salary Monthly], EmployeeSalaryTbl[Employee],$A$18#, EmployeeSalaryTbl[Salary Start Date],"<="&$L$1#, EmployeeSalaryTbl[Salary End Date],">="&$L$1#),0))
The data for the Project defined by image 2 is:
| Employee | Role | Discipline | Start Date | End Date | Salary Monthly |
|---|---|---|---|---|---|
| Bob | Senior Programmer | Programming | 12/01/2020 | 06/05/2020 | £4,333 |
| Dave | Mid Level Programmer | Programming | 01/02/2020 | 30/05/2020 | £3,167 |
| Peter | Senior Programmer | Programming | 01/01/2020 | 31/01/2020 | £4,583 |
| Jack | Junior Programmer | Programming | 01/02/2020 | 30/06/2020 | £2,083 |
| Richard | Senior Artist | Art | 01/03/2020 | 30/04/2020 | £3,750 |
| Rodney | Lead QA | QA | 01/03/2020 | 30/06/2020 | £4,333 |
| Proj 1 - Hire 1 | Senior Producer | Production | 01/02/2020 | 30/05/2020 | £3,458 |
| Roger | QA | QA | 01/01/2020 | 30/04/2020 | £1,667 |
| Wesley | Mid Level Programmer | Programming | 01/02/2020 | 31/05/2020 | £3,750 |
| Rachel | Senior Artist | Art | 01/01/2020 | 30/06/2020 | £3,333 |
| Proj 1 - Hire 2 | Lead Programmer | Programming | 01/01/2020 | 31/07/2020 | £4,417 |
And the data in for the EmployeeSalaryTbl is:
| Employee | Salary Start Date | Salary End Date | Salary | Salary Monthly | Salary Daily |
|---|---|---|---|---|---|
| Bob | 01/01/2020 | 31/03/2021 | £52,000 | £4,333 | £199 |
| Bob | 01/04/2021 | 31/03/2022 | £55,000 | £4,583 | £211 |
| Bob | 01/04/2022 | 31/12/9999 | £58,000 | £4,833 | £222 |
| Dave | 01/01/2020 | 31/03/2021 | £38,000 | £3,167 | £146 |
| Dave | 01/04/2021 | 31/12/9999 | £42,000 | £3,500 | £161 |
| Wesley | 01/01/2020 | 31/12/9999 | £45,000 | £3,750 | £173 |
| Jack | 01/01/2020 | 31/12/9999 | £25,000 | £2,083 | £96 |
| Richard | 01/01/2020 | 31/12/9999 | £45,000 | £3,750 | £173 |
| Rodney | 01/01/2020 | 31/12/9999 | £52,000 | £4,333 | £199 |
| Proj 1 - Hire 1 | 01/01/2020 | 31/12/9999 | £41,500 | £3,458 | £159 |
| Roger | 01/01/2020 | 31/12/9999 | £20,000 | £1,667 | £77 |
| Steve | 01/01/2020 | 31/12/9999 | £27,000 | £2,250 | £104 |
| Rachel | 01/01/2020 | 31/12/9999 | £40,000 | £3,333 | £153 |
| Peter | 01/01/2020 | 31/12/9999 | £34,000 | £2,833 | £130 |
| Sarah | 01/01/2020 | 31/12/9999 | £22,000 | £1,833 | £84 |
| Chloe | 01/01/2020 | 31/12/9999 | £33,000 | £2,750 | £127 |
| Matthew | 01/01/2020 | 31/03/2021 | £23,000 | £1,917 | £88 |
| Matthew | 01/04/2021 | 31/12/9999 | £28,000 | £2,333 | £107 |
| Proj 1 - Hire 2 | 01/01/2020 | 31/12/9999 | £36,000 | £3,000 | £138 |
CodePudding user response：
With your input data, and assuming no excel version constraints (not specified in the question), in cell H2 put the following formula:
=LET(namePrj, TB_Prj[Employee], startPrj, TB_Prj[Start Date], endPrj,
TB_Prj[End Date],name, TB_Roster[Employee],start, TB_Roster[Salary Start Date],
end, TB_Roster[Salary End Date],salary, TB_Roster[Salary Monthly],
SOMs, H1:S1, EOMs, EOMONTH(SOM,0),
BYCOL(SOMs, LAMBDA(SOM, LET(EOM, EOMONTH(SOM,0),
namesActive, FILTER(namePrj, (startPrj <= SOM) * (endPrj >= EOM)),
cost, FILTER(salary, (start <= SOM) * (IF(end > 0, end, EOM) >= EOM) *
(ISNUMBER(XMATCH(name,namesActive))),0), sum(cost)
)))
)
Two Excel Tables were defined to have dynamic ranges: TB_Prj for project information and TB_Roster for salary information. The month information is generated horizontally as follows in H1:
=EDATE(DATE(2020,1,1), SEQUENCE(1, 12, 0))
It generates the first day of the month. Represented in the formula as SOMs (Start Of the Month). Similarly, I use EOMs to represent each corresponding End Of the Month.
The output generates a 1x12 array as result.
Here is the output:
Showing only partially the salary table (TB_Roster)
Notes:
Please check the result, your data needs to be cleaned, for example, Peter in Salary Table has two entries overlapping the date interval.
If you cannot use tables (for example the input is based on SPILL formula like FILTER), then ranges can be used instead. You can define the following two data set ranges: prjSet, rosterSet, then define each corresponding column like this: INDEX(rng,,x), where x represents the corresponding column number on each range where rng is prjSet or rosterSet. For example name variable could be defined like this:
namePrj, INDEX(prjSet,,1)
Explanation
Using Excel Tables to have dynamic ranges, so the formula doesn't need to change when additional information is added. We use LET function to make the formula easier to maintain, defining the required inputs and intermediate results.
BYCOL is used to iterate over all SOMs values. For each start of the month (SOM), we do the following:
Find the namesActive from active projects checking that the start of the project is before or equal to SOM and the end of the project is greater or equal to EOM. In case no names from the project table satisfy the condition, FILTER returns an error (#CALC!) because empty arrays are not permitted in Excel. If this situation wants to be treated the formula needs to be adjusted (using a third input argument of FILTER and IF condition for computing cost) or better to clean the data. I would not expect for a given month not having any active project at a company level.
Once we have the list of names, we go to the second Excel Table (TB_Roster) to find a valid salary based on start, end dates.
We treat empty values in end, assuming that if there is no value (data is formatted as date, so empty means 0). The following IF condition ensures it (in the second FILTER call):
(IF(end > 0, end, EOM) >= EOM)
so if end array is positive (dates are represented as positive integers starting from 0, i.e. whole numbers), then a value was assigned, so we use this value, otherwise (0) it is replaced by EOM. When there is no end date the condition it is always true (all end values are considered). IF works with arrays, it evaluates the condition on each element of the array and returns the corresponding value on each element. By doing this we fixed the case of empty value to a valid situation, so there is no need to use a fictitious date.
The condition related to XMATCH, ensures we filter name by only the names on the previous FILTER call (valid names from the project table, i.e. namesActive). The result of this filter (cost) satisfies all the conditions and returns all matching monthly salaries.
In case there is no match based on the filter conditions, we return 0 (but another value can be used, for example NA()). In order to do that we use the third input argument of FILTER function.
Finally, we sum all the salaries that match the condition.
Bonus: Checking Interval Consistency
Based on the answer to the question: 