Hello! I would like to get the year spend for new hires in a company based on their expected start date and percent time allocation. I have a sheet with multiple departments, salary budget, percent allocation for hires (i.e., 100%, 50%, etc), and expected start date. So what I would like to do is:
For Department "X" and for people who have been Hired (i.e. my two conditions):
- total sum of the multiplication of two columns -- salary budget * %allocation. (so, the sum of, for example, someone hired at $100,000 and for 85% of their time ($100,000*.85), plus, someone hired for $150,000 and 100% of their time ($150000*1). I am thinking the formula has to have my two "IFS"/Conditionals --> {{Range Department}, "Department", {Range Status}, "Hired") -- and then the sum of the multiplication of the two columns. That said, I dont know how to do the sum of the multiplications of two columns if those two conditional statements are met.
- Then I would want to estimate the amount of the year that Hired person will actually be working to get the salary spend for that person in 2022 (i.e., if their salary is $100K for the year, but they start in October and are only working 3 months, then they would only cost $25K to the company this year). So it would be DATE(2022, 12, 31) - {EXPECTED START DATE} * (1/365). I would want this calculation to have the same above conditions.
- Then I would multiple the output of steps 1 and 2
I believe I have the logic/steps down, but am struggling with the formulas. Thank you in advance for your help!! My goal is to create a dashboard that shows the annualized spend and also the actual spend for 2022 for new hires.
