excel formula

Copper 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%)