asked on

If statement with a twist---URGENT

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

HainKurt

put this function into Alt+F11

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

Open in new window

spudmcc

ASKER

Lots of zero's and nothing else. ??????

ASKER CERTIFIED SOLUTION

hitsdoshi1

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HainKurt

lol :) I thought you have numbers between -100 & +100
I guess you have numbers between -1 & +1

spudmcc

ASKER

This worked great! I so appreciate all of your help, patience and talent. Thank you so much.

spudmcc (Andy)