Solved

If statement with a twist---URGENT

Posted on 2011-02-16
5
227 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:spudmcc
  • 2
  • 2
5 Comments
 
LVL 51

Expert Comment

by:HainKurt
ID: 34908456
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

0
 

Author Comment

by:spudmcc
ID: 34908707
Lots of zero's and nothing else.  ??????
0
 
LVL 9

Accepted Solution

by:
hitsdoshi1 earned 500 total points
ID: 34908804
There you go... I changed the code to reflect the % value.

Credit to HainKurt.
Function getValue(L As Double) As Double

If L >= 0 And L <= 0.05 Then
getValue = 0
ElseIf L >= 0.1 And L <= 0.2 Then
getValue = 0.15
ElseIf L >= 0.21 And L <= 0.4 Then
getValue = 0.3
ElseIf L >= 0.41 And L <= 0.6 Then
getValue = 0.5
ElseIf L >= 0.61 And L <= 0.8 Then
getValue = 0.7
ElseIf L < 0 And L >= -0.09 Then
getValue = 0
ElseIf L < -0.1 And L >= -0.2 Then
getValue = -0.15
ElseIf L < -0.21 And L >= -0.4 Then
getValue = -0.3
ElseIf L < -0.41 And L >= -0.6 Then
getValue = -0.5
ElseIf L < -0.61 And L >= -0.8 Then
getValue = -0.7
ElseIf L < -0.81 And L >= -0.99 Then
getValue = -0.8
Else
getValue = 0
End If

Open in new window

0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34908875
lol :) I thought you have numbers between -100 & +100
I guess you have numbers between -1 & +1
0
 

Author Closing Comment

by:spudmcc
ID: 34934609
This worked great!  I so appreciate all of your help, patience and talent.  Thank you so much.

spudmcc (Andy)
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now