Scientific to Decimal
in [Visual Basic]
|
From: Webbiz on 1 Dec 2009 01:48 On Mon, 30 Nov 2009 21:21:52 -0500, "MikeD" <nobody(a)nowhere.edu> wrote: > > >"Webbiz" <nospam(a)noway.com> wrote in message >news:blo8h5l82ecd6c0efqg64j7s4smq29cgq9(a)4ax.com... >> I'm trying to display the results of an averaging function in the same >> decimal format of the values that were averaged. Yet, the results end >> up in Scientific Notation, which is not desired. >> >> So I added the Format() function to the averaging function, but it is >> not returning any values at all. >> >> I've been searching the net for the reason why, but have not found >> anything other than the fomat I'm using. >> >> What am I doing wrong? >> >> Private Function DisplayAvgATR(ByRef Ranges() As Single) As String >> Dim n As Long >> Dim sngTotal As Single >> Dim sngAvg As Single >> >> For n = 0 To UBound(Ranges) >> sngTotal = sngTotal + Ranges(n) >> Next n >> >> >> >> 'Return the Average >> sngAvg = sngTotal / (UBound(Ranges) + 1) >> DisplayAvgATR = Format(sngAvg, "#.#") >> >> End Function >> >> While sngAvg actually has a value, say 6.0028E-02, it is not being >> returned as .060028. > > >Why would it when your format string is "#.#'? That's going to format it to >just one decimal place. Use as many # signs for the decimal part as you >want decimal places. > >And just to nitpick a bit here, since your function is not really displaying >anything, you might want to consider renaming it...for example CalcAvgATR or >even just simply CalcAvg since it will calculate the average for anything >that meets the parameter criteria of an array of type Single (kinda making a >guess that ATR indicates a set of specific values). > >And one more nitpick. Your function is making an assumption that the passed >in array will be 0-based. That's probably OK since that's the default for an >array. But you can make it generic so that it will work regardless of what >the lower bound is by doing this: > >Private Function CalcAvg(ByRef Ranges() As Single) As String >Dim n As Long >Dim sngTotal As Single >Dim sngAvg As Single > > For n = LBound(Ranges) To UBound(Ranges) > sngTotal = sngTotal + Ranges(n) > Next n > > > > 'Return the Average > sngAvg = sngTotal / (UBound(Ranges) - LBound(Ranges) + 1) > CalcAvg = Format$(sngAvg, "#.######") > >End Function > > >And one last nitpick, since the function returns a String, use the String >version of the Format function, as shown above. > >Some people will even say there's no need for the sngAvg variable. Just do >this: > >CalcAvg = Format$(sngTotal / (UBound(Ranges) - LBound(Ranges) + 1), >"#.######") > >And strictly speaking they'd be right. There is no absolute reason to use a >local variable to hold the result of the calculation, but when I'm debugging >and stepping through code, I like to have the results of calculations >assigned to variables to make it easier to get a tooltip of the result of >that calculation in the IDE (and yes I know you could position the >mousepointer on an opening parenthesis or select the expression to get a >tooltip showing the result, but it's much easier to just position the >mousepointer within a variable name). And the millisecond (or less) that the >local variable is using memory...not an issue as far as I'm concerned. What a nitpicker. LOL! Hey, I deserve it. I know. The "#.#" was something I found online. Could not find anything that helped. Okay. I'll rename the thing and make it generic. Really haven't thought of using it again, but you never know. Thanks. :-) BTW, while you've no problem 'nitpicking' <g>, got any suggestions on a snazy way to set the format pattern of the "#.####" based on the way the values are formatted in the array to begin with? Webbiz
From: Webbiz on 1 Dec 2009 01:50 On Mon, 30 Nov 2009 22:54:47 -0500, "MikeD" <nobody(a)nowhere.edu> wrote: >I think your confusion might be because # works a little differently when >left of the decimal place. If you have a format string of "#.##" and the >value to format is 500.5, the formatted string will be "500.5" even though >there was only a single # character left of the decimal point in the format >string. Yes. That's what confused me. ;-) Thanks Mike (and all others who chimed in.) Webbiz
From: Dee Earley on 1 Dec 2009 03:53 On 01/12/2009 06:48, Webbiz wrote: > On Mon, 30 Nov 2009 21:21:52 -0500, "MikeD"<nobody(a)nowhere.edu> wrote: >> "Webbiz"<nospam(a)noway.com> wrote >>> Private Function DisplayAvgATR(ByRef Ranges() As Single) As String > > BTW, while you've no problem 'nitpicking'<g>, got any suggestions on > a snazy way to set the format pattern of the "#.####" based on the way > the values are formatted in the array to begin with? The values in the array wont have a format as they are Singles. Formatting only applies when you convert to a string for display purposes. If you want the caller to be able to control the output format, either return a single and let it format it or take the format string as a parameter. -- Dee Earley (dee.earley(a)icode.co.uk) i-Catcher Development Team iCode Systems
From: Rick Rothstein on 1 Dec 2009 03:55 > BTW, while you've no problem 'nitpicking' <g>, got any suggestions on > a snazy way to set the format pattern of the "#.####" based on the way > the values are formatted in the array to begin with? Just put 15 # signs after the decimal point and VB will do the rest... it will maintain the maximum number of decimal places that are required. One thing you may want to consider (as long as you are using dots for decimal places and no thousands separators in your Format pattern) is to wrap the Format function's result with a Val function call... this will remove the trailing dot in case the average comes out to be an integer result. Or did you ask that because you want to preserve trailing zeroes in case the result had less decimal places than the number with the most decimal places that was being averaged? -- Rick (MVP - Excel)
From: Larry Serflaten on 1 Dec 2009 10:19
"Rick Rothstein" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote >> BTW, while you've no problem 'nitpicking' <g>, got any suggestions on >> a snazy way to set the format pattern of the "#.####" based on the way >> the values are formatted in the array to begin with? > > Just put 15 # signs after the decimal point and VB will do the rest... it > will maintain the maximum number of decimal places that are required. One > thing you may want to consider (as long as you are using dots for decimal > places and no thousands separators in your Format pattern) is to wrap the > Format function's result with a Val function call... this will remove the > trailing dot in case the average comes out to be an integer result. Or did > you ask that because you want to preserve trailing zeroes in case the > result had less decimal places than the number with the most decimal > places that was being averaged? In some cases, trailing zeros are important... :-) LFS |