Results 1 to 3 of 3
  1. #1
    Join Date
    May 2007
    Posts
    5

    Unanswered: IF Statement help please

    Hello,
    I'm trying to do the following statement, but without any joy.

    I have a spreadsheet that looks at live contracts and their variance(s) and I'm trying to do the following:

    Looking at the Earned, Profiled and Paid figures I want to:

    If Earned 10-20% below Profile then = 3
    If Earned >20% below Profile then = 2
    If Earned is >10% below Profile AND Earned is greater than Paid = 1

    This is the formula I have:
    =IF(L6="Live",IF((P6/N6)>0.1,IF(P6>O6,1,IF((P6/N6)>0.2,3,2)),0),0)

    Column L = status (IE - live contract or not)
    Column N = Profile
    Column O = Paid
    Column P = Earned

    It either gives me a 1 or a 3, but I'm not entirley sure I've got the percentage calculation 100% correct.

    Any help would be appreicated please!
    Last edited by Eraserhead; 05-22-07 at 09:20.

  2. #2
    Join Date
    May 2007
    Posts
    5
    I think I've got the IF part of it right:

    If = Live
    Then If statement 1
    Else = zero

    If statement 1
    If 10% adrift, then If statement 2
    Else = zero

    If statement 2
    If Earned > Paid
    Then = One
    Else If statement 3

    If statement 3
    If >20% adrift
    Then = Three
    Else = Two

  3. #3
    Join Date
    Feb 2007
    Posts
    348
    I put your exact statement as pasted below into excel and got all the possible results
    For 0 just make L anything other than live or N=200,0=5,P=10
    For 1 = 10, 1, 10 or 50,1,10
    for 2 = 50, 10, 10
    For 3 = 10, 10, 10

    so it does what it should do. As for it giving you the actually information you want, I can't know that.

Posting Permissions

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