Home

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
katkatk
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"

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%)
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
12 Replies