Thread: If condition

Hi All,
I need help to figure this out.
Sales Made Weightage
>25 15%
16 to 25 12 to 14.5%
11 to 15 7% to 10%
5 to 10 1% to 5%
0 to 4 0%

I am trying to calculate there performance depending on their sales.
If an agent made 16 sales he should get 12 points.
If an agent make sales between 16 to 25 then it should be prorated between 12 to14.5 points.

I'm not really clear from your example what your trying to acheive, what do you mean someone would recieve 12 14.5 points prorated? if someon had sales of 18 or 20 what would your expected answer be? maybe you could alter the example to show a little clearer what your input would be to get your desired output

Try this user defined function

Function GetPoints(Cell As Range)
Select Case Cell.Value
    Case Is > 25
        GetPoints = 15
    Case Is >= 16
        GetPoints = 12 + ((Cell.Value - 16) / (25 - 16)) * (14.5 - 12)
    Case Is >= 11
        GetPoints = 7 + ((Cell.Value - 11) / (15 - 11)) * (10 - 7)
    Case Is >= 5
        GetPoints = 1 + ((Cell.Value - 5) / (10 - 5)) * (5 - 1)
    Case Else
        GetPoints = 0
End Select
End Function

Open the VBA editor (Alt F11), insert a new module, paste in the above, return to the worksheet (Alt F11) and use the function  = GetPoints()

You can use mrices case statements and make a nested IF function...

My suggestion was to transform this:

Case Is > 25
        GetPoints = 15
    Case Is >= 16
        GetPoints = 12 + ((Cell.Value - 16) / (25 - 16)) * (14.5 - 12)
    Case Is >= 11
        GetPoints = 7 + ((Cell.Value - 11) / (15 - 11)) * (10 - 7)
    Case Is >= 5
        GetPoints = 1 + ((Cell.Value - 5) / (10 - 5)) * (5 - 1)
    Case Else
        GetPoints = 0

into this:

=IF(A1> 25,15,IF(A1>= 16,12 + ((Cell.Value - 16) / (25 - 16)) * (14.5 - 12),IF(A1>= 11,7 + ((Cell.Value - 11) / (15 - 11)) * (10 - 7),IF(A1>= 5,1 + ((Cell.Value - 5) / (10 - 5)) * (5 - 1),0))))

which is a formula!

My suggestion was to transform this:

Case Is > 25
        GetPoints = 15
    Case Is >= 16
        GetPoints = 12 + ((Cell.Value - 16) / (25 - 16)) * (14.5 - 12)
    Case Is >= 11
        GetPoints = 7 + ((Cell.Value - 11) / (15 - 11)) * (10 - 7)
    Case Is >= 5
        GetPoints = 1 + ((Cell.Value - 5) / (10 - 5)) * (5 - 1)
    Case Else
        GetPoints = 0

into this:

=IF(A1> 25,15,IF(A1>= 16,12 + ((A1 - 16) / (25 - 16)) * (14.5 - 12),IF(A1>= 11,7 + ((A1 - 11) / (15 - 11)) * (10 - 7),IF(A1>= 5,1 + ((A1 - 5) / (10 - 5)) * (5 - 1),0))))

which is a formula!