Creating inout values for excel
 

I am doing a weather related project, and I need to enter wind data. How
would I be able to enter the wind direction "N" and have it display "0",
NNE=1, NE=2... up to 15 so that it is easier to run a regression analysis on?

Creating inout values for excel
 

Easiest way is via a vlookup

List the references into cols A and B in Sheet2
N 0
NNE 1
etc

Then in Sheet1,
if wind dir. data will be entered in A2 down, eg: NNE, N, etc
you could place this in B2:
=IF(A2="","",VLOOKUP(A2,Sheet2!A:B,2,0))
and copy B2 down as far as required
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik
---
"nelly007" wrote:
I am doing a weather related project, and I need to enter wind data. How
would I be able to enter the wind direction "N" and have it display "0",
NNE=1, NE=2... up to 15 so that it is easier to run a regression analysis on?

Creating inout values for excel
 

Yeah I thought of that but I really dont want to have cells for both a
direction and a value for that direction

"Max" wrote:

Easiest way is via a vlookup

List the references into cols A and B in Sheet2
N 0
NNE 1
etc

Then in Sheet1,
if wind dir. data will be entered in A2 down, eg: NNE, N, etc
you could place this in B2:
=IF(A2="","",VLOOKUP(A2,Sheet2!A:B,2,0))
and copy B2 down as far as required
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik
---
"nelly007" wrote:
I am doing a weather related project, and I need to enter wind data. How
would I be able to enter the wind direction "N" and have it display "0",
NNE=1, NE=2... up to 15 so that it is easier to run a regression analysis on?

Creating inout values for excel
 

So you want to type NNE in a cell and have it change in-cell to a number?

You can do that with event code but how will you error-check your input?

Here is sheet event code to change in-cell as you enter but I think you're
looking for trouble with no way to cross-check entries.

Edit vals and nums arrays for more values. I just posted 10

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A100")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
vals = Array("N", "NE", "NW", "NNE", "NNW", "S", "SE", "SW", "SSE", "SSW")
nums = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
For Each rr In r
ival = 0
For i = LBound(vals) To UBound(vals)
If UCase(rr.Value) = vals(i) Then
ival = nums(i)
End If
Next
If ival 0 Then
rr.Value = ival
End If
Next
End Sub


Gord Dibben MS Excel MVP

On Wed, 10 Dec 2008 16:43:01 -0800, nelly007
wrote:

Yeah I thought of that but I really dont want to have cells for both a
direction and a value for that direction

"Max" wrote:

Easiest way is via a vlookup

List the references into cols A and B in Sheet2
N 0
NNE 1
etc

Then in Sheet1,
if wind dir. data will be entered in A2 down, eg: NNE, N, etc
you could place this in B2:
=IF(A2="","",VLOOKUP(A2,Sheet2!A:B,2,0))
and copy B2 down as far as required
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik
---
"nelly007" wrote:
I am doing a weather related project, and I need to enter wind data. How
would I be able to enter the wind direction "N" and have it display "0",
NNE=1, NE=2... up to 15 so that it is easier to run a regression analysis on?

Creating inout values for excel
 

If you don't want the values on the sheet you could use a defined name
formula for a regression variable:

DirNum:
=MATCH(WindDir,{"N";"NNE";"NE";"ENE";"E";"ESE";"SE ";"SSE";"S";"SSW";"SW";"WSW";"W";"WNW";"NW";"NNW"} ,0)-1

where WindDir is the range of values.

Then use in the formula eg: =LINEST(y_Values,DirNum,,1)
or if you are using more than one variable, you can use the syntax:
=LINEST(y_Values,CHOOSE({1,2,3},DirNum,x2_Values,x 3_Values),,1)



"nelly007" wrote:

Yeah I thought of that but I really dont want to have cells for both a
direction and a value for that direction

"Max" wrote:

Easiest way is via a vlookup

List the references into cols A and B in Sheet2
N 0
NNE 1
etc

Then in Sheet1,
if wind dir. data will be entered in A2 down, eg: NNE, N, etc
you could place this in B2:
=IF(A2="","",VLOOKUP(A2,Sheet2!A:B,2,0))
and copy B2 down as far as required
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik
---
"nelly007" wrote:
I am doing a weather related project, and I need to enter wind data. How
would I be able to enter the wind direction "N" and have it display "0",
NNE=1, NE=2... up to 15 so that it is easier to run a regression analysis on?

Creating inout values for excel
 

Gord,

How do I get "N" to return as zero in your sub?

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik
---
"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
So you want to type NNE in a cell and have it change in-cell to a number?

You can do that with event code but how will you error-check your input?

Here is sheet event code to change in-cell as you enter but I think you're
looking for trouble with no way to cross-check entries.

Edit vals and nums arrays for more values. I just posted 10

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A100")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
vals = Array("N", "NE", "NW", "NNE", "NNW", "S", "SE", "SW", "SSE", "SSW")
nums = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
For Each rr In r
ival = 0
For i = LBound(vals) To UBound(vals)
If UCase(rr.Value) = vals(i) Then
ival = nums(i)
End If
Next
If ival 0 Then
rr.Value = ival
End If
Next
End Sub


Gord Dibben MS Excel MVP

Creating inout values for excel
 

Hi Max

Change the ival initial value and re-define nums array.

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A100")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
vals = Array("N", "NE", "NW", "NNE", "NNW", "S", "SE", "SW", "SSE", "SSW")
nums = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9)
For Each rr In r
ival = -1
For i = LBound(vals) To UBound(vals)
If UCase(rr.Value) = vals(i) Then
ival = nums(i)
End If
Next
If ival -1 Then
rr.Value = ival
End If
Next
End Sub


Gord

On Thu, 11 Dec 2008 18:30:24 +0800, "Max" wrote:

Gord,

How do I get "N" to return as zero in your sub?

Creating inout values for excel
 

Many thanks, Gord. Works good

Max

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Hi Max

Change the ival initial value and re-define nums array.

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A100")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
vals = Array("N", "NE", "NW", "NNE", "NNW", "S", "SE", "SW", "SSE", "SSW")
nums = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9)
For Each rr In r
ival = -1
For i = LBound(vals) To UBound(vals)
If UCase(rr.Value) = vals(i) Then
ival = nums(i)
End If
Next
If ival -1 Then
rr.Value = ival
End If
Next
End Sub


Gord

Creating inout values for excel
 

Thaks Max

You're dabbling now<g

Just for interest's sake........................

Rick Rothstein's revision with minimal looping.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ival as Long
Set R = Range("A1:A100")
If Intersect(Target, R) Is Nothing Or Target.Count 1 Then Exit Sub
Application.EnableEvents = False
Vals = Array("C", "D", "G", "H", "K", "L", "O", "S", "C", "X")
Nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 8, 15)
For i = LBound(Vals) To UBound(Vals)
If UCase(Target.Value) = Vals(i) Then ival = Nums(i)
Next
Target.Value = ival
Application.EnableEvents = True
End Sub

Bob Phillips and Bernie Dietrick also showed me how to put the vals and nums
in ranges and use that instead.

Values in G1:G10.....................Numbers in H1:H10

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ival As Long
Dim R As Range
Set R = Range("A1:A100")
If Intersect(Target, R) Is Nothing Then Exit Sub
If Not IsError(Application.Match(Target.Value, _
Me.Range("G1:G10"), 0)) Then
ival = Application.VLookup(Target.Value, _
Me.Range("G1:H10"), 2, False)
Target.Value = ival
End If
End Sub

Either of these should have been posted instaed of the loopy thing I posted.


Gord

On Fri, 12 Dec 2008 06:49:18 +0800, "Max" wrote:

Many thanks, Gord. Works good

Max

"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Hi Max

Change the ival initial value and re-define nums array.

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A100")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
vals = Array("N", "NE", "NW", "NNE", "NNW", "S", "SE", "SW", "SSE", "SSW")
nums = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9)
For Each rr In r
ival = -1
For i = LBound(vals) To UBound(vals)
If UCase(rr.Value) = vals(i) Then
ival = nums(i)
End If
Next
If ival -1 Then
rr.Value = ival
End If
Next
End Sub


Gord

Creating inout values for excel
 

Thanks for sharing, Gord

Good options all, great for the archives too

Max