Hoping someone can help me with an issue I'm having.
I'm working with a payout grid. So for example if you fall on Curve 2 and attained 102% quota you get paid $500, if you attained 103% you get paid $525, 104% / $550, etc.
I recently learned that the field attainments need to be rounded to the nearest tenth. So for example I have someone with a 102.98% attainment who I was giving a payout of $500 when he should be rounded to 103% and be given $525.
The way I set up my code it checks if the attainment is less than the % in the payout grid. Once that statement is true it pulls the payment amount from the grid depending on which column you fall on.
So I have a person XX that falls in Curve 2 and has a 102.98% attainment. I omitted using the VBA round function due to the bankers rounding. I was using Application.WorksheetFunction.Round but wasn't getting the correct numbers when auditing the data. I went back to my original code to do some testing and after playing around for a bit I noticed the following issue - When I have 102.98% as a value It gives me the correct $500 amount, when I have 103.1% as a value I get back the correct $525 amount, but when the value is exactly 103% I get the lower $500 payout which doesn't make sense because even when those are equal the Boolean statement 103% < 103% should still return a FALSE.
I have the following code, my For loop starts looping through each rep and depending on which curve they fall in it starts looping through the grid with the While loop:
I'm working with a payout grid. So for example if you fall on Curve 2 and attained 102% quota you get paid $500, if you attained 103% you get paid $525, 104% / $550, etc.
I recently learned that the field attainments need to be rounded to the nearest tenth. So for example I have someone with a 102.98% attainment who I was giving a payout of $500 when he should be rounded to 103% and be given $525.
The way I set up my code it checks if the attainment is less than the % in the payout grid. Once that statement is true it pulls the payment amount from the grid depending on which column you fall on.
Attainment | Curve 1 | Curve 2 | Curve 3 |
| 102% | $600 | $500 | $400 |
| 103% | $625 | $525 | $425 |
| 104% | $650 | $550 | $450 |
So I have a person XX that falls in Curve 2 and has a 102.98% attainment. I omitted using the VBA round function due to the bankers rounding. I was using Application.WorksheetFunction.Round but wasn't getting the correct numbers when auditing the data. I went back to my original code to do some testing and after playing around for a bit I noticed the following issue - When I have 102.98% as a value It gives me the correct $500 amount, when I have 103.1% as a value I get back the correct $525 amount, but when the value is exactly 103% I get the lower $500 payout which doesn't make sense because even when those are equal the Boolean statement 103% < 103% should still return a FALSE.
I have the following code, my For loop starts looping through each rep and depending on which curve they fall in it starts looping through the grid with the While loop:
VBA Code:
'PAYOUT CALCULATIONS'
For i = 2 To lastrow
x = 1
'XXXX PAYOUT'
'CURVE 1'
If Sheets("Projected Attainment").Cells(i, 37).Value = 1 Then
While x < lastrowPG
x = x + 1
If Sheets("Projected Attainment").Cells(i, 14).Value < Sheets("Payout Grid").Cells(x + 1, 1) Then
If Sheets("Projected Attainment").Cells(i, 1).Value = "XX" Then
Sheets("Projected Attainment").Cells(i, 11).Value = Sheets("Payout Grid").Cells(x, 5).Value
ElseIf Sheets("Projected Attainment").Cells(i, 1).Value = "XY" Then
Sheets("Projected Attainment").Cells(i, 11).Value = Sheets("Payout Grid").Cells(x, 5).Value * 1.2
End If
x = lastrowPG
End If
Wend
End If
'CURVE 2'
If Sheets("Projected Attainment").Cells(i, 37).Value = 2 Then
While x < lastrowPG
x = x + 1
If Sheets("Projected Attainment").Cells(i, 14).Value < Sheets("Payout Grid").Cells(x + 1, 1) Then
If Sheets("Projected Attainment").Cells(i, 1).Value = "XX" Then
Sheets("Projected Attainment").Cells(i, 11).Value = Sheets("Payout Grid").Cells(x, 6).Value
ElseIf Sheets("Projected Attainment").Cells(i, 1).Value = "XY" Then
Sheets("Projected Attainment").Cells(i, 11).Value = Sheets("Payout Grid").Cells(x, 6).Value * 1.2
End If
x = lastrowPG
End If
Wend
End If
'CURVE 3'
If Sheets("Projected Attainment").Cells(i, 37).Value = 3 Then
While x < lastrowPG
x = x + 1
If Sheets("Projected Attainment").Cells(i, 14).Value < Sheets("Payout Grid").Cells(x + 1, 1) Then
If Sheets("Projected Attainment").Cells(i, 1).Value = "XX" Then
Sheets("Projected Attainment").Cells(i, 11).Value = Sheets("Payout Grid").Cells(x, 7).Value
ElseIf Sheets("Projected Attainment").Cells(i, 1).Value = "XY" Then
Sheets("Projected Attainment").Cells(i, 11).Value = Sheets("Payout Grid").Cells(x, 7).Value * 1.2
End If
x = lastrowPG
End If
Wend
End If
Next i
