User Defined Function for Wind Chill
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 |
User Defined Function for Wind Chill
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 "Carroll" wrote in message oups.com... 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 |
User Defined Function for Wind Chill
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 "Carroll" wrote in message oups.com... 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 |
User Defined Function for Wind Chill
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 "Carroll" wrote in message oups.com... 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 |
User Defined Function for Wind Chill
Wouldn't you want to convert back to provide an answer in Centigrade of Not
Using_Fahrenheit? -- Regards, Tom Ogilvy "Dana DeLouis" wrote in message ... 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 "Carroll" wrote in message oups.com... 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 |
User Defined Function for Wind Chill
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 |
User Defined Function for Wind Chill
Thanks. I remember reading something about not everyone agreeing with
the formula for the Wind Chill Index. |
User Defined Function for Wind Chill
Here's one link that lists both equations:
http://www.crh.noaa.gov/lsx/vortex/newwindchill.htm#Old -- Dana DeLouis Win XP & Office 2003 "Carroll" wrote in message oups.com... Thanks. I remember reading something about not everyone agreeing with the formula for the Wind Chill Index. |
User Defined Function for Wind Chill
On 27 Mar 2005 12:31:29 -0800, "Carroll" wrote:
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 |
User Defined Function for Wind Chill
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 In article , says... On 27 Mar 2005 12:31:29 -0800, "Carroll" wrote: 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 |
User Defined Function for Wind Chill
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 "Tushar Mehta" wrote in message om... 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 In article , says... On 27 Mar 2005 12:31:29 -0800, "Carroll" wrote: 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 |
User Defined Function for Wind Chill
Oops. Here's the web site:
http://www.nws.noaa.gov/om/windchill/ 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" -- Dana DeLouis Win XP & Office 2003 <snip |
User Defined Function for Wind Chill
On Tue, 29 Mar 2005 07:57:35 -0500, Tushar Mehta
wrote: 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 |
User Defined Function for Wind Chill
In article ,
says... {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 |
All times are GMT +1. The time now is 09:30 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com