Wind Speed ..... I need an Excel formula!

[geoffw]

Can anyone who is an expert at creating Excel formulae (or has a friend who can) help me?

I want a formula to convert a figure for wind speed m/s so that the Beaford Scale text desciption can be included

M/S M/S
Min Max
0.0 0.4 --- Calm
0.4 1.7 --- Light air
1.8 3.5 --- Light breeze
3.6 5.8 --- Gentle breeze
5.8 8.4 --- Moderate breeze
8.5 11.1 -- Fresh breeze
11.2 14.3 -- Strong breeze
14.3 17.4 -- Moderate gale
17.4 21.0 -- Fresh gale
21.0 24.5 -- Strong gale
24.6 28.6 -- Whole gale
28.6 33.0 -- Storm
33.1 37.6 -- Hurricane

[steve]

Here's some VBA to do it. The numbers and descriptions aren't quite the same as yours, they're the ones I use in Cumulus which came from the UKMO. But you can edit those bits to suit.

Code: Select all

Function Beaufort(speed As Double)
  If speed < 0.3 Then
    Beaufort = "Calm"
  ElseIf speed < 1.6 Then
    Beaufort = "Light air"
  ElseIf speed < 3.4 Then
    Beaufort = "Light breeze"
  ElseIf speed < 5.5 Then
    Beaufort = "Gentle breeze"
  ElseIf speed < 8# Then
    Beaufort = "Moderate breeze"
  ElseIf speed < 10.8 Then
    Beaufort = "Fresh breeze"
  ElseIf speed < 13.9 Then
    Beaufort = "Strong breeze"
  ElseIf speed < 17.2 Then
    Beaufort = "Near gale"
  ElseIf speed < 20.8 Then
    Beaufort = "Gale"
  ElseIf speed < 24.5 Then
    Beaufort = "Strong gale"
  ElseIf speed < 28.5 Then
    Beaufort = "Storm"
  ElseIf speed < 32.7 Then
    Beaufort = "Violent storm"
  Else
    Beaufort = "Hurricane"
  End If
End Function

[geoffw]

Thanks Steve ..... I'll take your figures as I can't remember where I go the other from.

now to delve into the mysteries of VBA!!!!!

[steve]

It's fairly straightforward; in Excel do Alt-F11 to bring up the VB editor, then choose "Insert | module" from the menu and paste the code into the window. The Beaufort function then becomes available in your spreadsheet like any other.

[geoffw]

I decided to stick with what I know .... a complicated nested +IF statement.

=IF(B5>=32.7,"Hurricane",IF(B5>=28.5,"Violent Storm",IF(B5>=24.5,"Storm",IF(B5>=20.8,"Strong Gale",IF(B5>=17.2,"Gale",IF(B5>=13.9,"Near Gale",IF(B5>=10.8,"Strong Breeze",IF(B5>=8,"Fresh Breeze,",IF(B5>=5.5,"Moderate Breeze",IF(B5>=3.4,"Gentle Breeze",IF(B5>=1.6,"Light Breeze",IF(B5>=0.3,"Light air","Calm"))))))))))))

Here it is if anyone wants it .... obviously the cell reference is up to you.

Anyone prepared to check I have the correct readings in the formula?

[steve]

The problem you have there is that you are applying Beaufort numbers/descriptions to gusts, whereas they actually apply to average/sustained speeds.

[geoffw]

The problem you have there is that you are applying Beaufort numbers/descriptions to gusts, whereas they actually apply to average/sustained speeds.

Am I? ....... Oh, how boring ....... the "Gusts" are much more exciting!

Anyway now i know what to do I suppose I can apply it to both!