SOLVED

Need Help with a Nest Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2933132%22%20slang%3D%22en-US%22%3ENeed%20Help%20with%20a%20Nest%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2933132%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20formula%20that%20will%20look%20at%20the%20start%20date%20of%20a%20hire%2C%20the%20starting%20salary%20and%20then%20increment%20the%20salary%20by%20a%20referenced%20percentage%20upon%20the%20anniversary%20of%20start%20date.%3CSPAN%20class%3D%22%22%3E%26nbsp%3B%20%3C%2FSPAN%3EHowever%2C%20the%20formula%20only%20works%20for%20the%20first%20anniversary%2C%20and%20I%20need%20to%20update%20for%20each%20successive%20anniversary.%20%26nbsp%3BMy%20brain%20is%20full.%20%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%3A%20IF(AND(G%248%26gt%3B%3D%24E14%2COR(%24F14%2B30%26gt%3BG%248%2C%24F14%3D0))%2CIF(G%248-%24E14%26gt%3B365%2C(%24D14*(1%2B%24B%244))%2F12%2C%24D14%2F12)%2C0)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2933132%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2933822%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20with%20a%20Nest%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2933822%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1208968%22%20target%3D%22_blank%22%3E%40gclarke%3C%2FA%3E%26nbsp%3BIn%20H13%2C%20try%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DIF(%24F13%26lt%3B%3DH%248%2C%24E13*(1%2B%24C%244)%5EINT(DATEDIF(%24F13%2CH%248%2C%22m%22)%2F12)%2F12%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3ECopy%20down%20and%20across.%20I'm%20not%20a%20big%20fan%20of%20the%20DATEDIF%20function%2C%20but%20in%20this%20case%20it%20works%20just%20fine.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2933934%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20with%20a%20Nest%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2933934%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1208968%22%20target%3D%22_blank%22%3E%40gclarke%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20I%20illustrated%20in%20the%20attached%20version%20of%20your%20file%2C%20the%20formula%20in%20H13%20is%3A%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DIF(H%248%26lt%3B%24F13%2C0%2C%0A(%24E13%2F12)*(1%2B%24C%244)%5E%0AINT(YEARFRAC(%24F13%2CH%248-1%2C1)))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2934206%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20with%20a%20Nest%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2934206%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3BMuch%20gratitude!%20Always%20nice%20to%20learn%20something!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have a formula that will look at the start date of a hire, the starting salary and then increment the salary by a referenced percentage upon the anniversary of start date.  However, the formula only works for the first anniversary, and I need to update for each successive anniversary.  My brain is full.   

 

The formula: IF(AND(G$8>=$E14,OR($F14+30>G$8,$F14=0)),IF(G$8-$E14>365,($D14*(1+$B$4))/12,$D14/12),0)

 

Thank you in advance!

6 Replies

@gclarke In H13, try this:

=IF($F13<=H$8,$E13*(1+$C$4)^INT(DATEDIF($F13,H$8,"m")/12)/12,0)

Copy down and across. I'm not a big fan of the DATEDIF function, but in this case it works just fine. 

@gclarke 

As I illustrated in the attached version of your file, the formula in H13 is: 

=IF(H$8<$F13,0,
($E13/12)*(1+$C$4)^
INT(YEARFRAC($F13,H$8-1,1)))

@Twifoo Much gratitude! Always nice to learn something!

 

Riny Thank as well, I appreciate the assistance
best response confirmed by gclarke (New Contributor)
Solution

@gclarke 

It's my pleasure to have enabled you to learn something. This is my first time to make a request from someone whom I've somehow helped. Could you kindly mark my previous reply as Best Response so that the number of my Best Responses crosses the two-digit line?

Absolutely! Done, and thank you again.