User Defined Function for Wind Chill

[Carroll]

Hello,

I was working on a user-defined function for the Wind Chill Index.
Here is what I came up with so far:

Function WindChill(WindSpeed_MPH, Temp_F°)

WindChill = Format((91.4 - (0.474677 - 0.020425 * WindSpeed_MPH +
0.303107 * WindSpeed_MPH ^ 0.5) * (91.4 - Temp_F°)), "0.0°")

End Function

I got the formula off the internet. I have seen where you can also
have a third parameter to define whether the temperature is Fahrenheit
or Celsius, but I won't worry about that as I never use Celsius, but
I'm sure that could be done as well. If I were to add it, I would make
it that if the third parameter was not used, then it would assume that
you were wanting to use Fahrenheit.

I was wanting to improve it somewhat. For instance, to put a message
right in the cell if two parameters haven't been provided, one or the
other was null, or not numeric. Since parameters can come from a cell
feeding the parameter, or put directly in the cell with the function, I
wasn't sure what was the best way to handle that was. Any ideas? I
wasn't able to get isblank(), etc. to work.

Also, when the function parameter wizard (I think that's the term) is
used, is there any way for me to provide help for that box?

Thanks,

Carroll Rinehart

 

[Tom Ogilvy]

The normal way it to let excel handle it for you

Function WindChill(WindSpeed_MPH as Double, Temp_F° as Double)

Excel would then return #Value! if the values were non numeric and would
notify the user during entry if an argument were missing. This is the way
other functions work and it would be best to mimic their behavior.

If you insist on your approach, you would need to use a paramarray so you
can have avariable number of arguments, then loop through it.

If you want the optional argument with a default value, then that is
explained as well. Look at the Function Statement in Excel VBA Help.


highlight the word Function in your function and hit F1. Choose VBA and you
should see the help.

--
Regards,
Tom Ogilvy




Hello,

I was working on a user-defined function for the Wind Chill Index.
Here is what I came up with so far:

Function WindChill(WindSpeed_MPH, Temp_F°)

WindChill = Format((91.4 - (0.474677 - 0.020425 * WindSpeed_MPH +
0.303107 * WindSpeed_MPH ^ 0.5) * (91.4 - Temp_F°)), "0.0°")

End Function

I got the formula off the internet. I have seen where you can also
have a third parameter to define whether the temperature is Fahrenheit
or Celsius, but I won't worry about that as I never use Celsius, but
I'm sure that could be done as well. If I were to add it, I would make
it that if the third parameter was not used, then it would assume that
you were wanting to use Fahrenheit.

I was wanting to improve it somewhat. For instance, to put a message
right in the cell if two parameters haven't been provided, one or the
other was null, or not numeric. Since parameters can come from a cell
feeding the parameter, or put directly in the cell with the function, I
wasn't sure what was the best way to handle that was. Any ideas? I
wasn't able to get isblank(), etc. to work.

Also, when the function parameter wizard (I think that's the term) is
used, is there any way for me to provide help for that box?

Thanks,

Carroll Rinehart

 

[Tom Ogilvy]

Functions in VBA don't reallly support help for the arguments. Laurent
Longre has built a free addin to support this function. It can be
downloaded at:

VBA functions don't support text descriptions in the Function wizard.


http://longre.free.fr/english/index.html#FunCustomize

--
Regards,
Tom Ogilvy



Hello,

I was working on a user-defined function for the Wind Chill Index.
Here is what I came up with so far:

Function WindChill(WindSpeed_MPH, Temp_F°)

WindChill = Format((91.4 - (0.474677 - 0.020425 * WindSpeed_MPH +
0.303107 * WindSpeed_MPH ^ 0.5) * (91.4 - Temp_F°)), "0.0°")

End Function

I got the formula off the internet. I have seen where you can also
have a third parameter to define whether the temperature is Fahrenheit
or Celsius, but I won't worry about that as I never use Celsius, but
I'm sure that could be done as well. If I were to add it, I would make
it that if the third parameter was not used, then it would assume that
you were wanting to use Fahrenheit.

I was wanting to improve it somewhat. For instance, to put a message
right in the cell if two parameters haven't been provided, one or the
other was null, or not numeric. Since parameters can come from a cell
feeding the parameter, or put directly in the cell with the function, I
wasn't sure what was the best way to handle that was. Any ideas? I
wasn't able to get isblank(), etc. to work.

Also, when the function parameter wizard (I think that's the term) is
used, is there any way for me to provide help for that box?

Thanks,

Carroll Rinehart

 

[Dana DeLouis]

Not sure, but some references give a newer version of that equation. If so,
here's is one of a few ways...

Function WindChill(Temp, MPH, Optional Using_Fahrenheit As Boolean = True)
'// = = = = = = = =
'// Use False in 3rd argument for Temp in C
'// = = = = = = = =

Dim V, T
V = MPH
T = Temp 'Default is in F°

'// If need, convert C° to F°
If Not Using_Fahrenheit Then T = 32 + 1.8 * T

WindChill = 0.4275 * V ^ 0.16 * T + 0.6215 * T - 35.75 * V ^ 0.16 +
35.74
WindChill = Round(WindChill)
End Function

--
Dana DeLouis
Win XP & Office 2003


Hello,

I was working on a user-defined function for the Wind Chill Index.
Here is what I came up with so far:

Function WindChill(WindSpeed_MPH, Temp_F°)

WindChill = Format((91.4 - (0.474677 - 0.020425 * WindSpeed_MPH +
0.303107 * WindSpeed_MPH ^ 0.5) * (91.4 - Temp_F°)), "0.0°")

End Function

I got the formula off the internet. I have seen where you can also
have a third parameter to define whether the temperature is Fahrenheit
or Celsius, but I won't worry about that as I never use Celsius, but
I'm sure that could be done as well. If I were to add it, I would make
it that if the third parameter was not used, then it would assume that
you were wanting to use Fahrenheit.

I was wanting to improve it somewhat. For instance, to put a message
right in the cell if two parameters haven't been provided, one or the
other was null, or not numeric. Since parameters can come from a cell
feeding the parameter, or put directly in the cell with the function, I
wasn't sure what was the best way to handle that was. Any ideas? I
wasn't able to get isblank(), etc. to work.

Also, when the function parameter wizard (I think that's the term) is
used, is there any way for me to provide help for that box?

Thanks,

Carroll Rinehart

 

[Tom Ogilvy]

Wouldn't you want to convert back to provide an answer in Centigrade of Not
Using_Fahrenheit?

 

[Carroll]

Tom,

I see you've added "double" to each parameter. I wanted to do something
like this, but I've not seen a good discussion of the various data
types, when to use, etc. Any links that you are aware of?

Carroll

 

[Carroll]

Thanks. I remember reading something about not everyone agreeing with
the formula for the Wind Chill Index.

 

[Dana DeLouis]

Here's one link that lists both equations:
http://www.crh.noaa.gov/lsx/vortex/newwindchill.htm#Old

 

[Tushar Mehta]

You ask good questions. Tom has already given you the answer as to how
XL by default would do things. While I usually let XL handle missing
arguments I don't like the way XL handles errors -- the error messages,
restricted to #NUM!, #VALUE!, and the like are too cryptic for my
taste.

Here's one way to approach your idea.

Function WindChill(optional WindSpeed_MPH, optional Temp_F) as variant
if ismissing(windspeed_mph) then
windchill="Hey, if you don't give me a windspeed in MPH," _
& " I cannot give you a windchill result!"
exit function
end if
if ismissing(temp_f) then
windchill="Tch, tch! If you don't give me a temp in" _
" farenheit, I cannot give you a windchill result!"
exit function
end if
...

A more practical approach, one that I would use for a simple function:

Function WindChill(WindSpeed_MPH as double, Temp_F as double, _
optional Use_Celcius as boolean=false) as variant
if windspeed_mph<0 then
windchill="Say what? You cannot have a negative wind speed!"
exit function
end if
...

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Ron Rosenfeld]

Function WindChill(WindSpeed_MPH, Temp_F°)

WindChill = Format((91.4 - (0.474677 - 0.020425 * WindSpeed_MPH +
0.303107 * WindSpeed_MPH ^ 0.5) * (91.4 - Temp_F°)), "0.0°")

End Function

I got the formula off the internet.

The formula you are using is the old definition. A few years ago it was
"updated" to :

= 35.74 + 0.6215*Temp - 35.75*Wind ^0.16 + 0.4275*Temp*Wind^0.16

for °F

For °C I just apply the conversion to the °F result.




--ron

 

[Tushar Mehta]

I know the formula is the same one as on the NOAA web site, but it
needs a 'threshold' to be applicable. To see why, just set the wind
speed to zero.

The NOAA web site implies -- but doesn't explicitly state that it is
valid for V >= 3. See http://www.erh.noaa.gov/er/iln/tables.htm

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Dana DeLouis]

At this NOAA web site, if you fill in a speed 0-3, you get an error message.
Their function returns the error message...

"Winds need to be above 3 MPH and below 110 MPH."

Looks like the temperature has a valid range also...
"Temperatures need to be above -50 °F and below 50 °F"
"Temperatures need be to above -50 °C and below 10 °C"

I was messing around with the equation. If working in C°, I believe either
of these will work.

wc = Round(13.1266666666667 + 0.6215 * t - 12.2611111111111 * v ^ (0.16) +
0.4275 * t * v ^ (0.16))
or
wc = Round((5 * (1539 * t - 44140) * v ^ (0.16) + 33 * (339 * t + 7160)) /
18000)

--
Dana DeLouis
Win XP & Office 2003


I know the formula is the same one as on the NOAA web site, but it
needs a 'threshold' to be applicable. To see why, just set the wind
speed to zero.

The NOAA web site implies -- but doesn't explicitly state that it is
valid for V >= 3. See http://www.erh.noaa.gov/er/iln/tables.htm

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Dana DeLouis]

Oops. Here's the web site:
http://www.nws.noaa.gov/om/windchill/

 

[Ron Rosenfeld]

I know the formula is the same one as on the NOAA web site, but it
needs a 'threshold' to be applicable. To see why, just set the wind
speed to zero.

You mean that an OAT of 32°F with no wind is NOT equivalent to a temp in the
60's?

It sure seems that way around here (downeast Maine) after a long, cold winter
)




--ron

 

[Tushar Mehta]

{snip}

You mean that an OAT of 32°F with no wind is NOT equivalent to a temp in the
60's?

It sure seems that way around here (downeast Maine) after a long, cold winter
)

--ron

LOL! Yeah, I know what you mean. Yesterday, the temp. was in the low
40s and people were out in shorts. [No, not me. Walking home from the
gym without proper protection is a prescription for problems. {g}]

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Carroll]

Thanks Tushar. Yes, I don't care for the cryptic way Excel handles
errors either. It could be too many things. Thanks for your ideas.

Carroll Rinehart