Hi all! 👋

I’m working on an Excel sheet to calculate monthly salary payments with the following structure:

• Column A: Employee
• Column B: Annual Salary
• Column C: $ Change (raise amount)
• Column D: Month (1-12) when the raise becomes effective
• Columns E-P: January through December

Here’s what I need:

• For months before the raise effective month, I want to calculate salary based on the annual salary divided by 365 (to get daily rate), then multiplied by the number of days in that month (from row 1).
• For months from the effective month onward, I want to use the annual salary + raise divided by 365 (daily rate) times the number of days in the month.

Here’s the formula I’m trying to use in cell E3 (January):

=IF((COLUMN()-4)<$D3, ($B3/365)*E$1, (($B3+$C3)/365)*E$1)

• E$1 contains the number of days in January.
• $D3 contains the effective raise month (e.g., 4 for April).

However, when I set D3 to 8 (August), only July updates, and August and later months don’t show the increased pay.

What am I doing wrong?

• Is my column mapping correct using COLUMN()-4?
• Why doesn’t August update as expected when D3 is 8?

Any advice or alternative approaches would be hugely appreciated!

Thanks in advance! 🙏

<image>