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

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%)
Related Conversations
Extentions Synchronization
ChirmyRam in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies