Hi All!

I have a large spreadsheet with nearly a million rows. I have a column (L)=Var% which is a percentage. What I need to do is create a column that tells the following:

if L >=0% but <=5% then '0'

if L >=10% but <=20% then '.15'

if L >=21% but <=40% then '.30'

if L >=41% but <=60% then '.50'

if L >=61% but <=80% then '.70'

if L <0% but >=-9% then '0'

if L <-10% but >=-20% then '-.15'

if L <-21% but >=-40% then '-.30'

if L <-41% but >=-60% then '-.50'

if L <-61% but >=-80% then '-.70'

if L <-81% but >=-99% then '-.80'

else '0'

Your help would be greatly appreciated.

spudmcc

I have a large spreadsheet with nearly a million rows. I have a column (L)=Var% which is a percentage. What I need to do is create a column that tells the following:

if L >=0% but <=5% then '0'

if L >=10% but <=20% then '.15'

if L >=21% but <=40% then '.30'

if L >=41% but <=60% then '.50'

if L >=61% but <=80% then '.70'

if L <0% but >=-9% then '0'

if L <-10% but >=-20% then '-.15'

if L <-21% but >=-40% then '-.30'

if L <-41% but >=-60% then '-.50'

if L <-61% but >=-80% then '-.70'

if L <-81% but >=-99% then '-.80'

else '0'

Your help would be greatly appreciated.

spudmcc

then use

B1 =getValue(A1)

assuming your data in in col A

copy down

```
Function getValue(L As Double) As Double
If L >= 0 And L <= 5 Then
getValue = 0
ElseIf L >= 10 And L <= 20 Then
getValue = 0.15
ElseIf L >= 21 And L <= 40 Then
getValue = 0.3
ElseIf L >= 41 And L <= 60 Then
getValue = 0.5
ElseIf L >= 61 And L <= 80 Then
getValue = 0.7
ElseIf L < 0 And L >= -9 Then
getValue = 0
ElseIf L < -10 And L >= -20 Then
getValue = -0.15
ElseIf L < -21 And L >= -40 Then
getValue = -0.3
ElseIf L < -41 And L >= -60 Then
getValue = -0.5
ElseIf L < -61 And L >= -80 Then
getValue = -0.7
ElseIf L < -81 And L >= -99 Then
getValue = -0.8
Else
getValue = 0
End If
End Function
```

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Credit to HainKurt.

Open in new window