Calculate variance outside band of two rates of pay

[Deadchef]

Hi

Slightly weird question and I'm hoping Excel is not going to let me down.

I've prepared an IF statement to show if a hourly rate of pay falls with a band of two pay rates and to return "YES" if it does and "NO" if it doesn't -

=IF(AND(R3>=T3,R3<=U3),"No","Yes")

If I get back "NO" I then want cell W3 to tell me how far outside the band the number is

e.g.

Current rate £10.00

Pay Banding £10.20 - £11.00 (In cells T3 & U3)

I want W3 to return the answer of "-0.20" but also if the current rate was "11.50" I'd need the answer to show as 0.50

Please help

Much appreciated

 

[AhoyNC]

Try:

IF(R3< T3,R3-T3,IF(U3< R3,R3-U3,""))

<r3,r3-u3,""))[ code]
<t3,r3-t3,if(u3<r3,r3-u3,""))[ code]

<t3,r3-t3,if(u3<r3,r3-u3,""))<t3,r3-t3,if(u3<r3,r3-u3,""))< td=""></t3,r3-t3,if(u3<r3,r3-u3,""))<t3,r3-t3,if(u3<r3,r3-u3,""))<> <tbody> </tbody>

<tbody>
</tbody>

</t3,r3-t3,if(u3<r3,r3-u3,""))[></r3,r3-u3,""))[>