Google Sheet - Array Formulas - Help with an additional argument to account for raises

[cmccambridge]

Hello,

Thanks in advance for the help.

Sample sheet is attached in Desktop Excel, but I need to adapt to Google Sheets

I have an array formula in Google sheets that pulls a monthly salary based on start date and current month, but I also need to add a 3% raise at each work anniversary??? Any suggestions??

So for employee one - I need a 3% increase starting 6/1/24 - 5/1/24, then an additional 3% starting 6/1/25-5/1/25.

Possible?, not overly complicated?

[oEx]

Possible, I have done it.

I added to your formula

Please Login or Register  to view this content.

so that the start salary increases every year.

You can adjust the raise in cell B17.

Try it.

[cmccambridge]

Thanks! Great Help.

Can you help converting that formula to google sheet Array. When I insert it does not translate.

Here is the formula with correct grid info: pulls correct number but no array
=IF($D16:$D1000<AA$14,$E16:$E1000*POWER(1+$P$11,ROUNDDOWN(DATEDIF($D16:$D1000,AA$15,"m")/12,0))/12,0)+IF(($D16:$D1000>=AA$14)*($D16:$D1000<=AA$15),((AA$15-$D16:$D1000)/365)*$E16:$E1000,0)

I tried this but It doesn't work:
=ARRAY_CONSTRAIN(ARRAYFORMULA(IF($D16:$D1000<AA$14,$E16:$E1000*POWER(1+$P$11,ROUNDDOWN(DATEDIF($D16:$D1000,AA$15,"m")/12,0))/12,0)+IF(($D16:$D1000>=AA$14)*($D16:$D1000<=AA$15),((AA$15-$D16:$D1000)/365)*$E16:$E1000,0)))

Any tips? Thanks!!

[oEx]

Sample sheet works fine in Google Sheets.

Without actual sheet I am afraid I am not able to help.

[cmccambridge]

Can you use this link. Google Sheets does not have table functions so I need to use an array formula so that the formula in E5 copies down the row automatically even if a row is added. right now you would have to drag down the formula when adding rows

http://https://docs.google.com/spreadsheets/d/1xRx3kEyFQQynG-C52ppFzYcJEUrPUS10/edit#gid=1566403933

[janmorris]

remove the ARRAY_CONSTRAIN()

then clear out all the cells under the first row

[cmccambridge]

I was unsuccessful following these instructions, its giving me a #value error. Can I get permission to use your formula and see if anyone has ideas to convert to an array formula?

https://docs.google.com/spreadsheets...gid=1566403933

[cmccambridge]

NEVERMIND! Fiddled and worked it out!

Thanks Again!