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!

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

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.