excel formula

%3CLINGO-SUB%20id%3D%22lingo-sub-533310%22%20slang%3D%22en-US%22%3Eexcel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-533310%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20anyone%20able%20to%20identify%20an%20issue%20with%20this%20formula%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF((TODAY()-D5%2F365.25%26gt%3B%3D45%2CG5-VLOOKUP(RIGHT(LEFT(C5%2C3)%2C2)%2Csalary2%2C2%2CFALSE)*8%25%2CIF(RIGHT(LEFT(C5%2C3)%2C2)%3D%22(PM)%20Project%20Manager%22%2CG5-VLOOKUP(RIGHT(LEFT(C5%2C3)%2C2)%2Csalary2%2C2%2CFALSE)*10%25%2CG5-VLOOKUP(RIGHT(LEFT(C5%2C3)%2C2)%2Csalary2%2C2%2CFALSE)*5%25))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-533310%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-533610%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-533610%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F334261%22%20target%3D%22_blank%22%3E%40katkatk%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ebetter%20to%20have%20some%20sample%20of%20what%20you'd%20like%20to%20calculate.%3C%2FP%3E%0A%3CPRE%3ETODAY()-D5%2F365.25%3C%2FPRE%3E%0A%3CP%3EIf%20in%20D5%20is%20some%20date%20it%20will%20be%20always%20more%20than%2045%2C%20perhaps%20you%20mean%20TODAY()-D5%3C%2FP%3E%0A%3CPRE%3ERIGHT(LEFT(C5%2C3)%2C2)%3C%2FPRE%3E%0A%3CP%3Ereturns%202nd%20and%203rd%20characters%20from%20the%20text%20in%20C5%2C%20it%20never%20will%20be%20equal%20to%26nbsp%3B%22(PM)%20Project%20Manager%22%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-534202%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-534202%22%20slang%3D%22en-US%22%3EPerhaps%2C%20this%20is%20the%20shorter%20version%20of%20the%20formula%20you%20need%3A%3CBR%20%2F%3E%3DVLOOKUP(RIGHT(LEFT(C5%2C3)%2C2)%2Csalary2%2C2%2C0)*%3CBR%20%2F%3ECHOOSE(((TODAY()-D5)%2F365.25%26gt%3B0)%2B(((TODAY()-D5)%2F365.25%26gt%3B44)*2)%2B(RIGHT(LEFT(C5%2C3)%2C2)%3D%22PM%22)%2C%3CBR%20%2F%3E5%25%2C10%25%2C8%25)%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi All! 

 

Is anyone able to identify an issue with this formula?

 

=IF((TODAY()-D5/365.25>=45,G5-VLOOKUP(RIGHT(LEFT(C5,3),2),salary2,2,FALSE)*8%,IF(RIGHT(LEFT(C5,3),2)="(PM) Project Manager",G5-VLOOKUP(RIGHT(LEFT(C5,3),2),salary2,2,FALSE)*10%,G5-VLOOKUP(RIGHT(LEFT(C5,3),2),salary2,2,FALSE)*5%))

2 Replies

@katkatk ,

 

better to have some sample of what you'd like to calculate.

TODAY()-D5/365.25

If in D5 is some date it will be always more than 45, perhaps you mean TODAY()-D5

RIGHT(LEFT(C5,3),2)

returns 2nd and 3rd characters from the text in C5, it never will be equal to "(PM) Project Manager"

Highlighted
Perhaps, this is the shorter version of the formula you need:
=VLOOKUP(RIGHT(LEFT(C5,3),2),salary2,2,0)*
CHOOSE(((TODAY()-D5)/365.25>0)+(((TODAY()-D5)/365.25>44)*2)+(RIGHT(LEFT(C5,3),2)="PM"),
5%,10%,8%)