Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Calculate variance outside band of two rates of pay

  1. #1
    New Member
    Join Date
    Aug 2017
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Calculate variance outside band of two rates of pay

    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

  2. #2
    Board Regular
    Join Date
    Oct 2011
    Posts
    3,838
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Calculate variance outside band of two rates of pay

    Try:
    Code:
    IF(R3< T3,R3-T3,IF(U3< R3,R3-U3,""))


    Last edited by AhoyNC; Sep 14th, 2018 at 08:25 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •