formula help.

[Keith (Southend)G]

I have a number of formulas in my weather spreadsheet but I want to
combine a couple and seem to be getting in a bit a muddle, I wonder if
someone could help.

Basically I have a column that converts wind speed from m/s to knots
after determining the maximum value, this works fine until this entry
is missing (no report), the formula then returns '0'. In these such
cases I would like it to return a 'x' to signify 'No report'

The formulas look something this atm...
=IF(COUNT(C10, H10, N100)=0,"-",MAX(C10, H10, N10))

then I want to include something like:
=MS_to_Knots(C10)
**
Function MS_to_Knots(ws As Variant)
If ws = "" Then
MS_to_Knots = "-"
Exit Function
End If
MS_to_Knots = 1.94254 * Val(ws)
End Function
**

Many thanks

Keith (Southend)

 

[Keith (Southend)G]

I have a number of formulas in my weather spreadsheet but I want to
combine a couple and seem to be getting in a bit a muddle, I wonder if
someone could help.

Basically I have a column that converts wind speed from m/s to knots
after determining the maximum value, this works fine until this entry
is missing (no report), the formula then returns '0'. In these such
cases I would like it to return a 'x' to signify 'No report'

The formulas look something this atm...
=IF(COUNT(C10, H10, N100)=0,"-",MAX(C10, H10, N10))

then I want to include something like:
=MS_to_Knots(C10)
**
Function MS_to_Knots(ws As Variant)
        If ws = "" Then
            MS_to_Knots = "-"
            Exit Function
        End If
        MS_to_Knots = 1.94254 * Val(ws)
    End Function
**

Many thanks

Keith (Southend)

AH, cracked it....
**
Function MS_to_Knots2(ws As Variant)
If ws = "-" Then
MS_to_Knots2 = "x"
Exit Function
End If
MS_to_Knots2 = 1.94254 * Val(ws)
End Function
**

 

[Ron Rosenfeld]

Function MS_to_Knots2(ws As Variant)
If ws = "-" Then
MS_to_Knots2 = "x"
Exit Function
End If
MS_to_Knots2 = 1.94254 * Val(ws)
End Function

Or, to avoid the Exit Function, you could do:

Function MS_to_Knots2(ws As Variant)
If ws = "-" Then
MS_to_Knots2 = "x"
else
MS_to_Knots2 = 1.94254 * Val(ws)
End If
End Function



--ron

 

[JE McGimpsey]

Just an alternative:


Public Function MS_to_Knots2(ws As Variant) As Variant
Const cdMS2K As Double = 1.94254
If IsNumeric(ws) Then
MS_to_Knots2 = ws * cdMS2K
Else
MS_to_Knots2 = "x"
End If
End Function

 

[Keith (Southend)G]

Many thanks for the replies, always very helpful on this group. I've
made a note of those other solutions as they may help me refine some
other anomalies I have.

Best regards

Keith (Southend)

 

[Dana DeLouis]

MS_to_Knots = 1.94254 * Val(ws)

Hi. I may be wrong, but a few references show a slightly different
conversion factor.

Here is a math program.

Convert[Meter/Second, Knot]

1.9438444924406049*Knot

Other references give the number in rationalized form...

900/463.
1.9438444924406049

Here is its inverse...

463/900.
0.5144444444444444...

Again, I may be wrong...
- - -
HTH
Dana DeLouis