Nested IF formula help please

[jonnieRC]

Hi there,

I was looking to use a formula whereby in the attached file I could do the following;

If the delta between the Actual average and the New rate is greater than 0.13 i need to move up to the next experience level (QARVP0 -> QARVP1). Then when comparing those two and the delta still is greater i need to just up again. The formula i have in E3 is close to what i need but obviously as i have a "1" in there i cannot change the 1 to a 2.

Then at the end (under Summary) i need to just pull the relevant Jobcode and then can VLOOKUP the correct rate to use.

Would be great if someone could help, I was thinking nested IF formula might be able to do it but i cannot get it to work.

Thanks in advance,

John

 

[NARAYANK991]

Hi ,

I am not sure I have understood you , but see if this works :

=IF(D3<0.15,SUBSTITUTE(A3,RIGHT(A3),MIN(RIGHT(A3)+1,RIGHT(Sheet2!$A$5)+0)),A3)

By the way , what is the purpose of using ABS(0.15) ?

Narayan

 

[bosco_yip]

Or……

Replaced your E3 formula :

=IF(D3<ABS(0.15),REPLACE(A3,6,1,1),A3)

By this formula, then copied down :

=IF(D3<ABS(0.15),REPLACE(A3,6,1,MIN(RIGHT(A3)+1,3)),A3)

Regards
Bosco

 

[jonnieRC]

Hi,

Thanks to you both (Bosco and Narayan) for your help.

That's perfect.

Thanks alot,
John