SOLVED

Merit Increase Schedule Formula

Contributor

I have a payroll file where I forecast out for the next five years how much base pay is with scenarios of merit earned by percentage. I have a working formula I created but wonder if there's an easier/more efficient way of completing this?

 

  • All years/month before current year are just actuals and brought over
  • Months 1-3 of current year takes merit of the previous year(s)
  • Months 4-12 of current year takes merit of previous year(s) on top of proposed merit increase
  • Future year(s)' 1-3 takes all previous year(s) merit increases
  • Future year(s)' 4-12 takes merit of previous year(s) on top of prosed merit increase

Essentially, months 1-3 should match months 4-12 and 4-12 then next year's 1-3 should be the same.

 

I wonder if I could use a CHOOSE or some other function that will do addition of an increase more efficiently?

Screen Shot 2022-03-28 at 11.55.57 AM.png

3 Replies
best response confirmed by Jpalaci1 (Contributor)
Solution

@Jpalaci1 

You may add helper data as in column G

image.png

and use formula

=[@[Salary Amount]]*(1+SUM( $F$1:INDEX($F$1:$F$7, MATCH( DATE([@Year],[@Month],1), $G$1:$G$7, 1 ) ) ))
Thank you so much! This worked.

@Jpalaci1 , you are welcome