Scientific to Decimal

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.

Thanks.

Webbiz
0
Webbiz
12/1/2009 12:29:27 AM
vb.general.discussion 1016 articles. 0 followers. Follow

40 Replies
691 Views

Similar Articles

[PageSpeed] 23

"Webbiz" <nospam@noway.com> wrote in message 
news:blo8h5l82ecd6c0efqg64j7s4smq29cgq9@4ax.com...
> While sngAvg actually has a value, say 6.0028E-02, it is not being
> returned as .060028.

It should return ".1" in that case, which is what I am getting. You asked 
only for one number after the decimal point, and Format rounds to that. If 
you need multiple digits, you need to add another parameter, 
NumDecimalPlaces. Example:

DisplayAvgATR = Format(sngAvg, "#." & String(NumDecimalPlaces, "#"))

Also look at Round() function, but due to precision, you may get 
0.060000000001 because in some cases a number cannot be precisely 
representing by binary values, so always use Format() function to display 
your data.


0
Nobody
12/1/2009 12:54:33 AM

"Webbiz" <nospam@noway.com> wrote in message 
news:blo8h5l82ecd6c0efqg64j7s4smq29cgq9@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.

-- 
Mike

 

0
MikeD
12/1/2009 2:21:52 AM
"MikeD" <nobody@nowhere.edu> wrote in
news:u9#uQ0icKHA.1592@TK2MSFTNGP06.phx.gbl: 

> 
> 
> "Webbiz" <nospam@noway.com> wrote in message 
> news:blo8h5l82ecd6c0efqg64j7s4smq29cgq9@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.

Isn't that use # for deciaml places if needed, or use 0 for required....

x = 7.12345

format (x,"##.############") will return 7.12345

while format (x,"00.000000000000") will return

07.12345000000





0
DanS
12/1/2009 3:04:03 AM

"DanS" <t.h.i.s.n.t.h.a.t@r.o.a.d.r.u.n.n.e.r.c.o.m> wrote in message 
news:Xns9CD3E0AA14322thisnthatroadrunnern@216.196.97.131...
> "MikeD" <nobody@nowhere.edu> wrote in
> news:u9#uQ0icKHA.1592@TK2MSFTNGP06.phx.gbl:
>
>>
>>
>> "Webbiz" <nospam@noway.com> wrote in message
>> news:blo8h5l82ecd6c0efqg64j7s4smq29cgq9@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.
>
> Isn't that use # for deciaml places if needed, or use 0 for required....
>
> x = 7.12345
>
> format (x,"##.############") will return 7.12345
>
> while format (x,"00.000000000000") will return
>
> 07.12345000000

You have to use as many placeholders in your format string as you want for 
minimum decimal places. He only had one decimal placeholder, so the function 
would only return one decimal.

If the format string is "#.##" and the value to format is .12345, the 
formatted string is going to be ".12" whereas if the format string is 
"#.#####", the formatted string will be ".12345". If the format string is 
"#.####" the formatted string will be ".1235" because that 5 in the 5th 
place is going to cause the 4 to get rounded up as the format string 
dictates only 4 decimal places (if I did all that correctly in my head).

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.


-- 
Mike
 

0
MikeD
12/1/2009 3:54:47 AM
On Mon, 30 Nov 2009 21:21:52 -0500, "MikeD" <nobody@nowhere.edu>
wrote:

>
>
>"Webbiz" <nospam@noway.com> wrote in message 
>news:blo8h5l82ecd6c0efqg64j7s4smq29cgq9@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
0
Webbiz
12/1/2009 6:48:11 AM
On Mon, 30 Nov 2009 22:54:47 -0500, "MikeD" <nobody@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
0
Webbiz
12/1/2009 6:50:02 AM
On 01/12/2009 06:48, Webbiz wrote:
> On Mon, 30 Nov 2009 21:21:52 -0500, "MikeD"<nobody@nowhere.edu> wrote:
>> "Webbiz"<nospam@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@icode.co.uk)
i-Catcher Development Team

iCode Systems
0
Dee
12/1/2009 8:53:00 AM
> 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) 

0
Rick
12/1/2009 8:55:54 AM

"Rick Rothstein" <rick.newsNO.SPAM@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 

0
Larry
12/1/2009 3:19:09 PM
Yep, that is why I added the last sentence/question. It was not entirely 
clear to me what Webbiz meant by "based on the way the values are formatted 
in the array to begin with".

-- 
Rick (MVP - Excel)


"Larry Serflaten" <serflaten@usinternet.com> wrote in message 
news:03F44C2F-9AF9-4FE9-9CAA-C87195AB2092@microsoft.com...
>
>
> "Rick Rothstein" <rick.newsNO.SPAM@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 

0
Rick
12/1/2009 5:02:28 PM
On Tue, 01 Dec 2009 08:53:00 +0000, Dee Earley
<dee.earley@icode.co.uk> wrote:

>On 01/12/2009 06:48, Webbiz wrote:
>> On Mon, 30 Nov 2009 21:21:52 -0500, "MikeD"<nobody@nowhere.edu> wrote:
>>> "Webbiz"<nospam@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.


I'm sorry, I didn't explain myself well. 

The data in the array is of a particular format although Singles.
Stock and Commodity prices come in various formats. Here are some
common ones:

Currencies:

0.9244 Australian Dollar
1.6638 British Pound
0.9581 Canadian Dollar
1.5093 Euro
1.1539 Japanese Yen

Meats:

82.625 Live Cattle
59.200 Lean Hogs

Softs:

70.31 Cotton
142.75 Coffee
22.17 Sugar

Financials:

122.31250 30yr T Bonds

Indexes:

1108.80 SP500

Grains:

399.75 Corn
1059.50 Soybeans
562.50 Wheat

Actual prices at the time of writing this.  :-)

So when one of these data files are loaded in, the output displays
would need to display results that are also in the appropriate format.

:-)

Webbiz

0
Webbiz
12/1/2009 8:07:20 PM
On Tue, 1 Dec 2009 03:55:54 -0500, "Rick Rothstein"
<rick.newsNO.SPAM@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?

Hello Rick.

My response to Dee's post may explain it better. But even a whole
number may require the decimal point if the original data format
requires it.

For example, if the data is for Soybeans, it would have two digits
following the decimal point even if the value is an integer, such as
1059.00.

Thanks!

Webbiz
0
Webbiz
12/1/2009 8:09:41 PM
"Webbiz" <nospam@noway.com> wrote in message 
news:bttah5hon83ti9i7qrsudkr5or3mh8qrno@4ax.com...

> My response to Dee's post may explain it better. But even a whole
> number may require the decimal point if the original data format
> requires it.
>
> For example, if the data is for Soybeans, it would have two digits
> following the decimal point even if the value is an integer, such as
> 1059.00.

You need more than an array of <some basic data type>. What you need is an 
array of classes or stuctures (VB Types) which have properties that further 
define the value you're storing. 


0
Jeff
12/1/2009 8:26:45 PM
"Webbiz" <nospam@noway.com> wrote in message 
news:bttah5hon83ti9i7qrsudkr5or3mh8qrno@4ax.com...

> For example, if the data is for Soybeans, it would have two
> digits following the decimal point [and hogs would have three]
> even if the value is an integer, such as 1059.00.

Dim LeanHogs As Single, Soybeans As Single
LeanHogs = 59
Soybeans = 1059
Print Format(LeanHogs, "0.000")
Print Format(Soybeans, "0.00")

Mike



0
Mike
12/1/2009 8:35:12 PM
Let Num be **any** number from the list of numbers that you are processing 
and let Avg be the calculated average...

Num = <<any value from list>>
Avg = <<calculated average>>
' This line formats the calculated value as per a number in the list
Avg = Format(Avg, "0." & String(Len(Num) - InStr(Num, "."), "0"))

-- 
Rick (MVP - Excel)


"Webbiz" <nospam@noway.com> wrote in message 
news:bttah5hon83ti9i7qrsudkr5or3mh8qrno@4ax.com...
> On Tue, 1 Dec 2009 03:55:54 -0500, "Rick Rothstein"
> <rick.newsNO.SPAM@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?
>
> Hello Rick.
>
> My response to Dee's post may explain it better. But even a whole
> number may require the decimal point if the original data format
> requires it.
>
> For example, if the data is for Soybeans, it would have two digits
> following the decimal point even if the value is an integer, such as
> 1059.00.
>
> Thanks!
>
> Webbiz 

0
Rick
12/1/2009 8:52:07 PM
On Tue, 1 Dec 2009 15:26:45 -0500, "Jeff Johnson" <i.get@enough.spam>
wrote:

>"Webbiz" <nospam@noway.com> wrote in message 
>news:bttah5hon83ti9i7qrsudkr5or3mh8qrno@4ax.com...
>
>> My response to Dee's post may explain it better. But even a whole
>> number may require the decimal point if the original data format
>> requires it.
>>
>> For example, if the data is for Soybeans, it would have two digits
>> following the decimal point even if the value is an integer, such as
>> 1059.00.
>
>You need more than an array of <some basic data type>. What you need is an 
>array of classes or stuctures (VB Types) which have properties that further 
>define the value you're storing. 
>


To answer yours and Mike's replies, the format has to be based on the
data loaded and not the name of the data loaded. This is because one
person may have a Lean Hogs file called LeanHogs, while someone else
may load lhz10, which is also Lean Hogs, and another may load LH_Rev,
etc. etc.

Otherwise, the user would have to define the data loaded manually,
telling the application how he wants the data displayed, etc. While
this is certainly the best approach for user customization purposes,
it is also an additional step that chartist like myself find tedious
to do. 

:-)
Webbiz
0
Webbiz
12/1/2009 9:20:30 PM
On Tue, 1 Dec 2009 15:52:07 -0500, "Rick Rothstein"
<rick.newsNO.SPAM@NO.SPAMverizon.net> wrote:

>Let Num be **any** number from the list of numbers that you are processing 
>and let Avg be the calculated average...
>
>Num = <<any value from list>>
>Avg = <<calculated average>>
>' This line formats the calculated value as per a number in the list
>Avg = Format(Avg, "0." & String(Len(Num) - InStr(Num, "."), "0"))

I'm going to try this one out.

Thanks!

Webbiz
0
Webbiz
12/1/2009 9:22:08 PM
On Tue, 1 Dec 2009 15:52:07 -0500, "Rick Rothstein"
<rick.newsNO.SPAM@NO.SPAMverizon.net> wrote:

>Let Num be **any** number from the list of numbers that you are processing 
>and let Avg be the calculated average...
>
>Num = <<any value from list>>
>Avg = <<calculated average>>
>' This line formats the calculated value as per a number in the list
>Avg = Format(Avg, "0." & String(Len(Num) - InStr(Num, "."), "0"))


Ah, tested but didn't work out because the blasted values in the array
are in Scientific Notation. VB had to have done this because the
actual values when loaded into the array were not in SN.

What causes this and how to keep VB from doing it this way?

Here is where this is happening:

    Values(1) = CurrPriceData.High - CurrPriceData.Low

Values() is type Single.

CurrPriceData.High = 0.5001
CurrPriceData.Low = 0.4963

Values(1) = 3.800005E-03

Why?

Thanks.

Webbiz
0
Webbiz
12/1/2009 9:30:49 PM
>>Let Num be **any** number from the list of numbers that you are processing
>>and let Avg be the calculated average...
>>
>>Num = <<any value from list>>
>>Avg = <<calculated average>>
>>' This line formats the calculated value as per a number in the list
>>Avg = Format(Avg, "0." & String(Len(Num) - InStr(Num, "."), "0"))
>
>
> Ah, tested but didn't work out because the blasted values in the array
> are in Scientific Notation. VB had to have done this because the
> actual values when loaded into the array were not in SN.
>
> What causes this and how to keep VB from doing it this way?
>
> Here is where this is happening:
>
>    Values(1) = CurrPriceData.High - CurrPriceData.Low
>
> Values() is type Single.
>
> CurrPriceData.High = 0.5001
> CurrPriceData.Low = 0.4963
>
> Values(1) = 3.800005E-03

Try using one of the CurrPriceData.High  or CurrPriceData.Low values for Num 
value in my expression as they seem like they might be in the right format 
(String as opposed to Single I'm guessing). If these values are really 
numeric values and not text String representations of those numerical 
values, then can you see the formatted number from the original source? If 
so, this is what you want to assign to the Num variable. Basically, you need 
a **formatted** value so you can see how many decimal places there are... if 
these are numerical values, then you will never know if the value you chose 
would have ended in zeroes when originally formatted (since numbers do not 
have format, they can end with zeroes after the decimal point, only 
formatted String representations of these numbers can do that).

-- 
Rick (MVP - Excel) 

0
Rick
12/1/2009 9:49:17 PM
"Webbiz" <nospam@noway.com> wrote in message 
news:ae2bh514mrcog5bovvdiuunvteeq6dgui4@4ax.com...
> Ah, tested but didn't work out because the blasted values in the array
> are in Scientific Notation. VB had to have done this because the
> actual values when loaded into the array were not in SN.

Single/Double are not stored in Scientific Notation, they are always stored 
in binary form. When you implicitly assign Single/Double to String, VB does 
the conversion for you, which may not be what you want. You have to use 
Format() function.

Also, Round() function converts a number from one binary form to another, so 
it could round something from .8999999999 to .900000001, so you still have 
to use Format() function to display the value to insure that you get the 
correct number of extra digits.

If you want to see what Single looks like in binary form, see here:

http://en.wikipedia.org/wiki/Single_precision_floating-point_format

And Double:

http://en.wikipedia.org/wiki/Double_precision_floating-point_format


0
Nobody
12/1/2009 10:24:43 PM
On Tue, 1 Dec 2009 17:24:43 -0500, "Nobody" <nobody@nobody.com> wrote:

>"Webbiz" <nospam@noway.com> wrote in message 
>news:ae2bh514mrcog5bovvdiuunvteeq6dgui4@4ax.com...
>> Ah, tested but didn't work out because the blasted values in the array
>> are in Scientific Notation. VB had to have done this because the
>> actual values when loaded into the array were not in SN.
>
>Single/Double are not stored in Scientific Notation, they are always stored 
>in binary form. When you implicitly assign Single/Double to String, VB does 
>the conversion for you, which may not be what you want. You have to use 
>Format() function.
>
>Also, Round() function converts a number from one binary form to another, so 
>it could round something from .8999999999 to .900000001, so you still have 
>to use Format() function to display the value to insure that you get the 
>correct number of extra digits.
>
>If you want to see what Single looks like in binary form, see here:
>
>http://en.wikipedia.org/wiki/Single_precision_floating-point_format
>
>And Double:
>
>http://en.wikipedia.org/wiki/Double_precision_floating-point_format
>


Then why are my Single values, showing in Watch as .5938 for example,
showing as a Scientific Notation value after some math and assigned to
another Single variable?

If I send the original single value to the text box, it will show up
as .5938. But do some subtraction, for example, of two of these
Singles and assign to another Single, or directly to the textbox, and
it displays as Scentific Notation?

Webbiz
0
Webbiz
12/1/2009 10:34:41 PM
On Tue, 1 Dec 2009 16:49:17 -0500, "Rick Rothstein"
<rick.newsNO.SPAM@NO.SPAMverizon.net> wrote:

>>>Let Num be **any** number from the list of numbers that you are processing
>>>and let Avg be the calculated average...
>>>
>>>Num = <<any value from list>>
>>>Avg = <<calculated average>>
>>>' This line formats the calculated value as per a number in the list
>>>Avg = Format(Avg, "0." & String(Len(Num) - InStr(Num, "."), "0"))
>>
>>
>> Ah, tested but didn't work out because the blasted values in the array
>> are in Scientific Notation. VB had to have done this because the
>> actual values when loaded into the array were not in SN.
>>
>> What causes this and how to keep VB from doing it this way?
>>
>> Here is where this is happening:
>>
>>    Values(1) = CurrPriceData.High - CurrPriceData.Low
>>
>> Values() is type Single.
>>
>> CurrPriceData.High = 0.5001
>> CurrPriceData.Low = 0.4963
>>
>> Values(1) = 3.800005E-03
>
>Try using one of the CurrPriceData.High  or CurrPriceData.Low values for Num 
>value in my expression as they seem like they might be in the right format 
>(String as opposed to Single I'm guessing). If these values are really 
>numeric values and not text String representations of those numerical 
>values, then can you see the formatted number from the original source? If 
>so, this is what you want to assign to the Num variable. Basically, you need 
>a **formatted** value so you can see how many decimal places there are... if 
>these are numerical values, then you will never know if the value you chose 
>would have ended in zeroes when originally formatted (since numbers do not 
>have format, they can end with zeroes after the decimal point, only 
>formatted String representations of these numbers can do that).


The values are stored in DataArray as Singles

Type DataArray
    dDate As Date
    Open As Single
    High As Single
    Low As Single
    Close As Single
End Type

So these are both Singles...

CurrPriceData.High = 0.5001
CurrPriceData.Low = 0.4963

And if I send these variables directly to the TextBox, they display
accordingly.

However, subtract one from the other and assign to the textbox or
another Single variable, and it displays as Sci-Notation. I find this
very odd.

Yes, I can use the original single variable to test for format,
although I'll have to store it in a Global as these values are static
to another function that has long lost scope.

Thanks. 
Webbiz


0
Webbiz
12/1/2009 10:42:05 PM
Webbiz wrote:

> Then why are my Single values, showing in Watch as .5938 for
> example, showing as a Scientific Notation value after some math and
> assigned to another Single variable?
>
> If I send the original single value to the text box, it will show up
> as .5938. But do some subtraction, for example, of two of these
> Singles and assign to another Single, or directly to the textbox,
> and it displays as Scentific Notation?

A textbox can't display a single, it can only display strings. And
anything you show in Watch or Debug etc is a also string.

If you want to control how something appears on the screen (i.e. in
the string domain), use Format.

-- 
   Jim Mack
   Twisted tees at http://www.cafepress.com/2050inc
   "We sew confusion"

0
Jim
12/1/2009 10:56:08 PM
Webbiz wrote:
....

> Then why are my Single values, showing in Watch as .5938 for example,
> showing as a Scientific Notation value after some math and assigned to
> another Single variable?
> 
> If I send the original single value to the text box, it will show up
> as .5938. But do some subtraction, for example, of two of these
> Singles and assign to another Single, or directly to the textbox, and
> it displays as Scentific Notation?

Because unless you control the output format specifically, you're at the 
mercy of the VB RTL i/o library logic for converting the internal 
representation to some default character representation.  What that will 
be will be determined by the magnitude and the implementation of the 
specific control or i/o operation.

If you care what the output looks like, use Format$()

BTW, this a generic requirement in any programming language that has the 
equivalent facility of providing default i/o formatting, not just VB. 
In Fortran, it's called "list-directed" formatting as just one example.

--
0
dpb
12/1/2009 11:10:32 PM
"Webbiz" <nospam@noway.com> wrote in message 
news:ha6bh5ps7b8qnab20lbqpultrc567oq2lm@4ax.com...
> Then why are my Single values, showing in Watch as .5938 for example,
> showing as a Scientific Notation value after some math and assigned to
> another Single variable?
>
> If I send the original single value to the text box, it will show up
> as .5938. But do some subtraction, for example, of two of these
> Singles and assign to another Single, or directly to the textbox, and
> it displays as Scentific Notation?

Because the way numbers are stored in binary form(fraction+Exponent), there 
can be two binary forms for the same decimal number. Kind of like saying 
100/500~101/499. This has implications when testing if two values are equal, 
which may not be what you expect. This is true for all languages, not just 
VB. To solve this, use something like "If Abs(x-y)<=0.01 Then" to test for 
equality, or write that as IsEqual() function for easy reading. See this 
article and related articles in the References section:

INFO: Visual Basic and Arithmetic Precision
http://support.microsoft.com/kb/279755

See this sample which prints the byte values so you can see the difference. 
It's based on the numbers that you have posted in an earlier reply:

Option Explicit

Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" ( _
    Destination As Any, Source As Any, ByVal Length As Long)

Private Sub Form_Load()
    Dim x As Single
    Dim y As Single

    x = 0.5001
    y = 0.4963

    y = x - y
    Debug.Print y
    PrintBinaryValue y

    x = 0.0038
    Debug.Print x
    PrintBinaryValue x

    If x = y Then
        Debug.Print "x = y"
    Else
        Debug.Print "x <> y"
    End If

    If Abs(x - y) < 0.01 Then
        Debug.Print "Almost equal"
    Else
        Debug.Print "Not equal"
    End If

End Sub

' Print "x" in binary form
Private Sub PrintBinaryValue(ByRef x As Single)
    Dim b(0 To 3) As Byte
    Dim i As Long

    CopyMemory b(0), x, 4
    For i = 0 To 3
        Debug.Print Hex(b(i)); " ";
    Next
    Debug.Print
End Sub



Output:

 3.800005E-03
80 9 79 3B
 0.0038
6C 9 79 3B
x <> y
Almost equal



0
Nobody
12/1/2009 11:20:08 PM
On Tue, 1 Dec 2009 17:56:08 -0500, "Jim Mack" <jmack@mdxi.nospam.com>
wrote:

>Webbiz wrote:
>
>> Then why are my Single values, showing in Watch as .5938 for
>> example, showing as a Scientific Notation value after some math and
>> assigned to another Single variable?
>>
>> If I send the original single value to the text box, it will show up
>> as .5938. But do some subtraction, for example, of two of these
>> Singles and assign to another Single, or directly to the textbox,
>> and it displays as Scentific Notation?
>
>A textbox can't display a single, it can only display strings. And
>anything you show in Watch or Debug etc is a also string.
>
>If you want to control how something appears on the screen (i.e. in
>the string domain), use Format.


Jim, I know this.

What I'm saying is that the value is changing from a regular decimal
value to Scientific Notation. So when I go to display it in a text
box, it shows as Scientific Notation.  Yes, format is what will need
to be used, and that's still in discussion in this thread. However, my
question is WHY is it changing to Sci-Not?

Thanks.

Webbiz
0
Webbiz
12/1/2009 11:49:37 PM
Thanks. Reading article now.  :)

On Tue, 1 Dec 2009 18:20:08 -0500, "Nobody" <nobody@nobody.com> wrote:

>"Webbiz" <nospam@noway.com> wrote in message 
>news:ha6bh5ps7b8qnab20lbqpultrc567oq2lm@4ax.com...
>> Then why are my Single values, showing in Watch as .5938 for example,
>> showing as a Scientific Notation value after some math and assigned to
>> another Single variable?
>>
>> If I send the original single value to the text box, it will show up
>> as .5938. But do some subtraction, for example, of two of these
>> Singles and assign to another Single, or directly to the textbox, and
>> it displays as Scentific Notation?
>
>Because the way numbers are stored in binary form(fraction+Exponent), there 
>can be two binary forms for the same decimal number. Kind of like saying 
>100/500~101/499. This has implications when testing if two values are equal, 
>which may not be what you expect. This is true for all languages, not just 
>VB. To solve this, use something like "If Abs(x-y)<=0.01 Then" to test for 
>equality, or write that as IsEqual() function for easy reading. See this 
>article and related articles in the References section:
>
>INFO: Visual Basic and Arithmetic Precision
>http://support.microsoft.com/kb/279755
>
>See this sample which prints the byte values so you can see the difference. 
>It's based on the numbers that you have posted in an earlier reply:
>
>Option Explicit
>
>Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" ( _
>    Destination As Any, Source As Any, ByVal Length As Long)
>
>Private Sub Form_Load()
>    Dim x As Single
>    Dim y As Single
>
>    x = 0.5001
>    y = 0.4963
>
>    y = x - y
>    Debug.Print y
>    PrintBinaryValue y
>
>    x = 0.0038
>    Debug.Print x
>    PrintBinaryValue x
>
>    If x = y Then
>        Debug.Print "x = y"
>    Else
>        Debug.Print "x <> y"
>    End If
>
>    If Abs(x - y) < 0.01 Then
>        Debug.Print "Almost equal"
>    Else
>        Debug.Print "Not equal"
>    End If
>
>End Sub
>
>' Print "x" in binary form
>Private Sub PrintBinaryValue(ByRef x As Single)
>    Dim b(0 To 3) As Byte
>    Dim i As Long
>
>    CopyMemory b(0), x, 4
>    For i = 0 To 3
>        Debug.Print Hex(b(i)); " ";
>    Next
>    Debug.Print
>End Sub
>
>
>
>Output:
>
> 3.800005E-03
>80 9 79 3B
> 0.0038
>6C 9 79 3B
>x <> y
>Almost equal
>
>
0
Webbiz
12/1/2009 11:52:06 PM
"Nobody" <nobody@nobody.com> wrote in message 
news:uR6jXztcKHA.1028@TK2MSFTNGP06.phx.gbl...
> Output:
>
> 3.800005E-03
> 80 9 79 3B
> 0.0038
> 6C 9 79 3B
> x <> y
> Almost equal


Here is a break down of byte values for the first number, which was a result 
of a calculation:

> 80 9 79 3B

In the correct order, this would be:

3B790980

Based on this article:

http://en.wikipedia.org/wiki/Single_precision_floating-point_format

Breaking to basic components(all decimal):

Sign: 0
Exponent: 118
Fraction: 7932288

Equation:

(-1)^Sign * 2 ^ (Exponent - 127) * (1 + Fraction / (2^23))

So the number would be(Using Windows Calculator):

0.0038000047206878662109375

And for the other number, which was entered manually in code(x = 0.0038):

> 6C 9 79 3B

In the correct order, this would be:

3B79096C

Breaking to basic components(all decimal):

Sign: 0
Exponent: 118
Fraction: 7932268

So the number would be:

0.003800000064074993133544921875

It's very close to first one. Here are the numbers again, in Hex and decimal 
format to the precision that Windows Calculator can handle:

3B790980
3B79096C

0.0038000047206878662109375
0.003800000064074993133544921875



0
Nobody
12/2/2009 12:09:35 AM
That's a problem then... once the numbers are placed in a numeric variable, 
they lose all their formatting (numbers are just numbers, they have no 
format). The problem with using a value from the list of numbers is that 
none of your values might be "full". For example, what if all your numbers 
are **supposed** to be shown to 3 decimal points, but all your values just 
happen to, by rare chance, have only one or two decimal places... if you try 
using one of these numbers in my expression, you will not get 3-decimal 
places shown. You also can't just select a number from the list because you 
might not get a "full" number either. Where are these numbers coming from? 
Perhaps you can go back to the source to get a representative formatted text 
representation of the number and use that.

-- 
Rick (MVP - Excel)


"Webbiz" <nospam@noway.com> wrote in message 
news:kh6bh5hd7f52vhq97kkumhgoerl125kgl1@4ax.com...
> On Tue, 1 Dec 2009 16:49:17 -0500, "Rick Rothstein"
> <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote:
>
>>>>Let Num be **any** number from the list of numbers that you are 
>>>>processing
>>>>and let Avg be the calculated average...
>>>>
>>>>Num = <<any value from list>>
>>>>Avg = <<calculated average>>
>>>>' This line formats the calculated value as per a number in the list
>>>>Avg = Format(Avg, "0." & String(Len(Num) - InStr(Num, "."), "0"))
>>>
>>>
>>> Ah, tested but didn't work out because the blasted values in the array
>>> are in Scientific Notation. VB had to have done this because the
>>> actual values when loaded into the array were not in SN.
>>>
>>> What causes this and how to keep VB from doing it this way?
>>>
>>> Here is where this is happening:
>>>
>>>    Values(1) = CurrPriceData.High - CurrPriceData.Low
>>>
>>> Values() is type Single.
>>>
>>> CurrPriceData.High = 0.5001
>>> CurrPriceData.Low = 0.4963
>>>
>>> Values(1) = 3.800005E-03
>>
>>Try using one of the CurrPriceData.High  or CurrPriceData.Low values for 
>>Num
>>value in my expression as they seem like they might be in the right format
>>(String as opposed to Single I'm guessing). If these values are really
>>numeric values and not text String representations of those numerical
>>values, then can you see the formatted number from the original source? If
>>so, this is what you want to assign to the Num variable. Basically, you 
>>need
>>a **formatted** value so you can see how many decimal places there are... 
>>if
>>these are numerical values, then you will never know if the value you 
>>chose
>>would have ended in zeroes when originally formatted (since numbers do not
>>have format, they can end with zeroes after the decimal point, only
>>formatted String representations of these numbers can do that).
>
>
> The values are stored in DataArray as Singles
>
> Type DataArray
>    dDate As Date
>    Open As Single
>    High As Single
>    Low As Single
>    Close As Single
> End Type
>
> So these are both Singles...
>
> CurrPriceData.High = 0.5001
> CurrPriceData.Low = 0.4963
>
> And if I send these variables directly to the TextBox, they display
> accordingly.
>
> However, subtract one from the other and assign to the textbox or
> another Single variable, and it displays as Sci-Notation. I find this
> very odd.
>
> Yes, I can use the original single variable to test for format,
> although I'll have to store it in a Global as these values are static
> to another function that has long lost scope.
>
> Thanks.
> Webbiz
>
> 

0
Rick
12/2/2009 12:41:28 AM
Webbiz wrote:
....
> question is WHY is it changing to Sci-Not?
....

Unless you're formatting it, the output format is at the mercy of the 
default i/o VB rtl...see other response for more specifically...

--
0
dpb
12/2/2009 1:04:07 AM
On Tue, 1 Dec 2009 19:41:28 -0500, "Rick Rothstein"
<rick.newsNO.SPAM@NO.SPAMverizon.net> wrote:

>That's a problem then... once the numbers are placed in a numeric variable, 
>they lose all their formatting (numbers are just numbers, they have no 
>format). The problem with using a value from the list of numbers is that 
>none of your values might be "full". For example, what if all your numbers 
>are **supposed** to be shown to 3 decimal points, but all your values just 
>happen to, by rare chance, have only one or two decimal places... if you try 
>using one of these numbers in my expression, you will not get 3-decimal 
>places shown. You also can't just select a number from the list because you 
>might not get a "full" number either. Where are these numbers coming from? 
>Perhaps you can go back to the source to get a representative formatted text 
>representation of the number and use that.


Here's an idea Rick.

The application requires a minimum of 3 years data to work properly
(this is not my charting program, btw). 

That's at least 600 data values, all Singles.

In such a scenario, it's not only rare, it's impossible to not find
the full format of the dataset.

So what do you think of this:

Within the routine that actually loads the data into the app, note
each value for the number of digits following the decimal. If I'm not
mistaken, the left is not important because "#. " will expand
accordingly. It's right of the decimal that needs to be determined.

So as each value passes by, if any value ends up with more values
right of the decimal, the format is adjusted. This format is then
saved in a Global String variable.

Therefore, what I'm looking for would be the maximum needed values
right of the decimal, right?

So if this were discovered, the string variable could be set by...

Select Case DecCnt
	Case 0
		sDataFormat = "#"
	Case 1
		sDataFormat = "#.#"
	Case 2
		sDataFormat = "#.##"

....etc.

Or is this too clumsy?

Webbiz

0
Webbiz
12/2/2009 1:11:17 AM
Webbiz wrote:
....

> Or is this too clumsy?


All depends on what you really need...if you can't a priori determine 
the number of decimal digits required I suppose that's one possibility 
if the data are always loaded from a text file.  If you were to load 
other floating point data it's possible you would always end up w/ the 
full precision of the variable owing to the rounding issues previously 
discussed.

I'd think knowing simply the actual magnitude of the numbers if these 
are prices would be sufficient to determine a reasonable estimate of the 
precision required but I suppose that may be too naive a solution.

--
0
dpb
12/2/2009 2:44:00 AM
"Webbiz" <nospam@noway.com> wrote in message
news:blo8h5l82ecd6c0efqg64j7s4smq29cgq9@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.
>

We've had this conversation before IIRC.

Frankly for your whole application you need to either use the VB Currency
datatype which will provide some consistency, or write your own 'Decimal'
datatype routines (probably better as you tend to flop about on precision)
that are based on integer math.

[OR - buy a math/financial/statistical package. Unfortunately they tend to
be pricey, unless you can use C/C++. There are many open source C/C++
libraries available.which you can use to create ActiveX or standard DLLs.]

No financial/statistical application that needs to be reliable, consistent,
and accurate uses floating datatypes for its historical data, journals,
accounts, or projections. And all math is integer unless floating math is
absolutely required - and even then the specific precision is rigorously
enforced going in and coming out.

-ralph


0
Ralph
12/2/2009 2:49:19 AM
Webbiz wrote:
> Jim Mack wrote:
>
>> Webbiz wrote:
>>
>>> Then why are my Single values, showing in Watch as .5938 for
>>> example, showing as a Scientific Notation value after some math
>>> and assigned to another Single variable?
>>>
>>> If I send the original single value to the text box, it will show
>>> up as .5938. But do some subtraction, for example, of two of these
>>> Singles and assign to another Single, or directly to the textbox,
>>> and it displays as Scentific Notation?
>>
>> A textbox can't display a single, it can only display strings. And
>> anything you show in Watch or Debug etc is a also string.
>>
>> If you want to control how something appears on the screen (i.e. in
>> the string domain), use Format.
>
>
> Jim, I know this.

Apparently not completely.

Singles do not have a 'format'. They cannot change from 'regular' to
'scientific'. That is strictly a function of being displayed as a
string. If you want them to appear a certain way then you have to
format them that way.

You are NOT seeing singles. You're seeing strings, which are the
result of formatting. Accept the default VB formatting, and you get
what VB decides. Use Format and get what you want. It's really pretty
simple.

-- 
   Jim Mack
   Twisted tees at http://www.cafepress.com/2050inc
   "We sew confusion"


>
> What I'm saying is that the value is changing from a regular decimal
> value to Scientific Notation. So when I go to display it in a text
> box, it shows as Scientific Notation.  Yes, format is what will need
> to be used, and that's still in discussion in this thread. However,
> my question is WHY is it changing to Sci-Not?
>
> Thanks.
>
> Webbiz

0
Jim
12/2/2009 3:53:33 AM
On Tue, 01 Dec 2009 19:11:17 -0600, Webbiz <nospam@noway.com> wrote:

>So if this were discovered, the string variable could be set by...
>
>Select Case DecCnt
>	Case 0
>		sDataFormat = "#"
>	Case 1
>		sDataFormat = "#.#"
>	Case 2
>		sDataFormat = "#.##"
>
>...etc.
>
>Or is this too clumsy?


I'm going to answer this one myself.

I wrote this function to do the above...

Public Sub SetDataFormat(ByVal Digits As Long)

    sDataFormat = "#"
    
    If Digits > 0 Then sDataFormat = sDataFormat & "." &
Right("###########", Digits)

End Sub


To feed it the 'Digits', I am doing the following:

 If DigitsRightOfDec(CStr(Price)) > nMaxNum Then
    nMaxNum = DigitsRightOfDec(CStr(Price))
 End If


And DigitsRightOfDec is done by...

Public Function DigitsRightOfDec(ByVal sNum As String) As Long

    If InStr(1, sNum, ".") > 0 Then
        DigitsRightOfDec = Len(sNum) - InStr(1, sNum, ".")
    End If

End Function


That's my solution short of having the user manually enter what the
format should be.

Webbiz

0
Webbiz
12/2/2009 5:58:48 AM
On 01/12/2009 20:07, Webbiz wrote:
> On Tue, 01 Dec 2009 08:53:00 +0000, Dee Earley
> <dee.earley@icode.co.uk>  wrote:
>
>> On 01/12/2009 06:48, Webbiz wrote:
>>> On Mon, 30 Nov 2009 21:21:52 -0500, "MikeD"<nobody@nowhere.edu>  wrote:
>>>> "Webbiz"<nospam@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.
>
>
> I'm sorry, I didn't explain myself well.
>
> The data in the array is of a particular format although Singles.
> Stock and Commodity prices come in various formats. Here are some
> common ones:
>
<SNIP>
>
> Actual prices at the time of writing this.  :-)
>
> So when one of these data files are loaded in, the output displays
> would need to display results that are also in the appropriate format.

But the input values do not have a format by definition of being a 
Single value.
Yes, they will have some amount of decimal places, but no specific 
"000.00000" format so in your example, the code will ONLY see:

82.625 Live Cattle
59.2 Lean Hogs

399.75 Corn
1059.5 Soybeans
562.5 Wheat

If your calling code wants a specific format, it should format the value 
it gets back (which will also be unformatted)
It is still much safer to pass numbers around as numbers rather than 
formatted strings, then only convert them for display.

Another example:
a=1.4444444:?a
  1.4444444
b=2.0000000:?b
  2
c=2.5555556:?c
  2.5555556
d=3.0000000:?d
  3
?(a+b+c+d)/4
  2.25

You can possibly determine the number of decimal places a number has by 
multiplying by 10 in a loop until it approaches 0 (Don't use exactly 0 
due to floating point maths)

-- 
Dee Earley (dee.earley@icode.co.uk)
i-Catcher Development Team

iCode Systems
0
Dee
12/2/2009 9:40:44 AM
On Wed, 02 Dec 2009 09:40:44 +0000, Dee Earley
<dee.earley@icode.co.uk> wrote:

>On 01/12/2009 20:07, Webbiz wrote:
>> On Tue, 01 Dec 2009 08:53:00 +0000, Dee Earley
>> <dee.earley@icode.co.uk>  wrote:
>>
>>> On 01/12/2009 06:48, Webbiz wrote:
>>>> On Mon, 30 Nov 2009 21:21:52 -0500, "MikeD"<nobody@nowhere.edu>  wrote:
>>>>> "Webbiz"<nospam@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.
>>
>>
>> I'm sorry, I didn't explain myself well.
>>
>> The data in the array is of a particular format although Singles.
>> Stock and Commodity prices come in various formats. Here are some
>> common ones:
>>
><SNIP>
>>
>> Actual prices at the time of writing this.  :-)
>>
>> So when one of these data files are loaded in, the output displays
>> would need to display results that are also in the appropriate format.
>
>But the input values do not have a format by definition of being a 
>Single value.
>Yes, they will have some amount of decimal places, but no specific 
>"000.00000" format so in your example, the code will ONLY see:
>
>82.625 Live Cattle
>59.2 Lean Hogs
>
>399.75 Corn
>1059.5 Soybeans
>562.5 Wheat
>
>If your calling code wants a specific format, it should format the value 
>it gets back (which will also be unformatted)
>It is still much safer to pass numbers around as numbers rather than 
>formatted strings, then only convert them for display.
>
>Another example:
>a=1.4444444:?a
>  1.4444444
>b=2.0000000:?b
>  2
>c=2.5555556:?c
>  2.5555556
>d=3.0000000:?d
>  3
>?(a+b+c+d)/4
>  2.25
>
>You can possibly determine the number of decimal places a number has by 
>multiplying by 10 in a loop until it approaches 0 (Don't use exactly 0 
>due to floating point maths)


What I ended up doing was to insert this code at the point where
prices are extracted by database and stored into a data array.

If DigitsRightOfDec(CStr(Samples(nArrCnt).High)) > nMaxNum Then
    nMaxNum = DigitsRightOfDec(CStr(Samples(nArrCnt).High))
End If

Whatever the maximum number of digits are right of the decimal, that
value is then used to create the format for all price displays for
that particular data set loaded.

Public Sub SetDataFormat(ByVal Digits As Long)

    sDataFormat = "#"
    
    If Digits > 0 Then sDataFormat = sDataFormat & "." &
Right("000000000", Digits)

End Sub

This sDataFormat is then used for all the Format() displays of the
data.

Seems to be working.

Thanks.

Webbiz


0
Webbiz
12/4/2009 7:32:04 PM
On Wed, 02 Dec 2009 20:28:16 GMT, deactivated wrote:

>On Tue, 01 Dec 2009 00:48:11 -0600, Webbiz <nospam@noway.com> wrote:
>
>>On Mon, 30 Nov 2009 21:21:52 -0500, "MikeD" <nobody@nowhere.edu>
>>wrote:
>
>>>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
>
>Should not a function do a single thing?
>
>Like calculate an average and return in the same type.
>
>Any user who wants to format the result can then put it in another
>function that would format the string.
>
>That way the user can reuse that average function elsewhere as it is
>almost generic.


I see your point.

What I've noticed in all the time I've been learning things from this
newsgroup is that the mindset is geared towards programming with the
idea that others may be reusing the code or needing to work with it.

Why my code tends not to usually come out this way is that I'm a lone
soul in a home office writing code that only I will ever use. 

But I agree that my mindset needs to be adjusted towards a more
reusable and cooperative mentality. And so I appreciate these
reminders.

:-)

Webbiz
0
Webbiz
12/4/2009 7:41:24 PM
"Webbiz" <nospam@noway.com> wrote in message 
news:3noih59pnegc5oa59j5h2346o9ehbi08k2@4ax.com...
> What I ended up doing was to insert this code at the point where
> prices are extracted by database and stored into a data array.
>
> If DigitsRightOfDec(CStr(Samples(nArrCnt).High)) > nMaxNum Then
>    nMaxNum = DigitsRightOfDec(CStr(Samples(nArrCnt).High))
> End If

Avoid using CStr() in this case. I think DigitsRightOfDec() looks for "." 
and CStr() would use the decimal separator in Control Panel, which could be 
"," or any other character. Use Str() instead, which always use US-English.

The same applies when using Val(). It recognizes only US-English, so use 
Str/Val combo, or CStr/SSng or CStr/CDbl() to translate back and forth, and 
when using Cxxx functions, make sure that the locale is still the same. If 
you intend on making users from different locales send data to each other, 
store the data in its binary form, or use Str/Val combo which uses fixed 
format to avoid locale issues.



0
Nobody
12/4/2009 7:59:23 PM
On Fri, 4 Dec 2009 14:59:23 -0500, "Nobody" <nobody@nobody.com> wrote:

>"Webbiz" <nospam@noway.com> wrote in message 
>news:3noih59pnegc5oa59j5h2346o9ehbi08k2@4ax.com...
>> What I ended up doing was to insert this code at the point where
>> prices are extracted by database and stored into a data array.
>>
>> If DigitsRightOfDec(CStr(Samples(nArrCnt).High)) > nMaxNum Then
>>    nMaxNum = DigitsRightOfDec(CStr(Samples(nArrCnt).High))
>> End If
>
>Avoid using CStr() in this case. I think DigitsRightOfDec() looks for "." 
>and CStr() would use the decimal separator in Control Panel, which could be 
>"," or any other character. Use Str() instead, which always use US-English.
>
>The same applies when using Val(). It recognizes only US-English, so use 
>Str/Val combo, or CStr/SSng or CStr/CDbl() to translate back and forth, and 
>when using Cxxx functions, make sure that the locale is still the same. If 
>you intend on making users from different locales send data to each other, 
>store the data in its binary form, or use Str/Val combo which uses fixed 
>format to avoid locale issues.
>
>


Interesting point I didn't think of. Now I would have to be concerned
whether the data being loaded itself would have a "," rather than a
"."

Hmmm.

thanks.

Webbiz
0
Webbiz
12/4/2009 9:57:43 PM
Reply:

Similar Artilces:

how do I turn off an Automatic Scientific Format
Every Time i want to Enter some Text in to Excel it is automaticall formatted as Scientific Text .. IE: I enter in 5E1 amd Excel Automatically formats the number an displays it as 5.00E+1 (50) and so on and so on for other code further down that are 5E4, 5E5 etc ... am I able to turn off thi Automatic Scientific Function that deems my codes are Exponentia Formulas -- Message posted from http://www.ExcelForum.com Hi not sure you can turn it off, but if you enter an apostraphe in front of the values it won't think they're numbers.. e.g. '5E1 alternatively, select where you a...

Scientific to Decimal
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...

The no. of decimal places used in a calculation
Without usign ROUNDUP or DOWN , is it possible to calculate a formul using the figure shown in the cell (i.e 2 decimal places) rather tha the underlying figure which could be to many more places? How many decimal places does Excel work to -- Message posted from http://www.ExcelForum.com Hi I believe Excel works to 15 decimal places. If you only want to use the places shown on your sheet you'll need to go to Tools / Options / Calculation and check 'Precision as displayed'. Be warned, this will lose all the other decimal places and you cannot undo this to get them back! -- And...

Decimal places in the result of formula in PWA2007
Hello, How can we manage the number of decimal places that PWA will display in the result of a calculated custom field? I just want 2 decimals, not 12... :) Thanks for help Robert -- Try adding the following formatting command to your formula: Format(YourFormulaHere, "#0.00") Hope this helps. -- Dale A. Howard [MVP] VP of Educational Services msProjectExperts http://www.msprojectexperts.com http://www.projectserverexperts.com "We write the books on Project Server" "Robert" <robert@libre.gaulle> wrote in message news:O...

Price List decimals
I am trying to figure out how to change the price list decimal to .001. Anyone have some sample coding to do this>? Thanks! Hi, An option you could use is in Settings/Settings/Organisation Settings/ System Settings/General/"Pricing Decimal Precision" Please do click on Yes or No if this post was helpful or not for our feedback. You can contact me directly at my eamil address if you need any MS CRM Help. -- take care, uMar Khan imumar@gmail.com "sbanker" wrote: > I am trying to figure out how to change the price list decimal to .001. > Anyone have some sam...

decimal & percent format
Hi, I have a spreadsheet where I want column Y to be in percent format. So I put the following line in the code: .Range("Y" & lRow).NumberFormat = "0.0%" It is working fine except for some user!? For them some of the data in the column show in decimal format (ex ..88) and some in percent (ex 88%). Can it be something to do with their local setting? Thank you. Guedj54 All I do is click on Format/Cells/Percentage and the cells are automatically formatted to be in %. Or, a faster way is to simply click on the "%" icon on top. Guedj54 wrote: > *Hi...

how do you type scientific notation in Word 2003?
I'm trying to type problems that add, subtract,etc, into scientific notation. Such as 1.2 x 10^3 + 2.3 x 10^6 so that the "3" and 6" are shown as an exponents. Hi skcole11, Simple: superscript the the "3" and 6". -- Cheers macropod [Microsoft MVP - Word] "skcole11" <skcole11@discussions.microsoft.com> wrote in message news:9295C87B-B5D7-4A8D-A44C-F9C7058F08AF@microsoft.com... > I'm trying to type problems that add, subtract,etc, into scientific notation. > Such as 1.2 x 10^3 + 2.3 x 10^6 so that the "3&q...

what is a scientific notation#?
what is a scientific notation The Scientific format displays a number in exponential notation, replacing part of the number with E+n, where E (which stands for Exponent) multiplies the preceding number by 10 to the nth power. For example, a 2-decimal Scientific format displays 12345678901 as 1.23E+10, which is 1.23 times 10 to the 10th power. Gord Dibben MS Excel MVP On Tue, 16 Feb 2010 10:17:01 -0800, hisham <hisham@discussions.microsoft.com> wrote: >what is a scientific notation Scientific notation is generally used for very large or very small number. In ...

Numbers converting to decimal
I a trying to figure out why when I type 11 and automatically converts it to .11, if I type 11. it will stay 11,if I change all the cells to text then back to number they willstay. I have checked the formatting of the cells, it even happens when I open a brand new worksheet. Any ideas? Thanks Dawn Hi Dawn, Tools>Option>Edit, uncheck Fixed Decimal -- Kind Regards, Niek Otten Microsoft MVP - Excel "DawnP" <anonymous@discussions.microsoft.com> wrote in message news:c3cf01c48a05$d75359d0$a501280a@phx.gbl... > I a trying to figure out why when I type 11 and &...

Removing decimal on Excel 97 from the Currency Column
I have a spreadsheet and I need to re-format the currency that now contains decimals example 133.50 and I need it to show 13350 (no decimals). I have tried everything and I cannot get the rid of the decimal. Can someone please tell me how to do this. Thanks! You can use a help column and a formula =--SUBSTITUTE(TEXT(A1,"0.00"),".","") or edit>replace and replace . with nothing of course it will return 1335 instead or 13350 which the formula will return -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussi...

DECIMAL v DOUBLE
I'm pretty new to SQL and am a little confused as to which field type I should use to store my 'currency' type data. What's the main difference between these two data types and which would best fit the purpose? I want to store signed numerical data with up to 6 decimal places. Thanks in advance for any help. DECIMAL is an exact numeric, whereas DOUBLE is approximate - which means that whilst you can store e.g. 1.234567 in a decimal and it's equal to 1.234567, for a double this may be stored as 1.23456699999999. Doubles can cause grief with compari...

scientific notation
so I'm importing excel data into access.. the problems fields are mostly text.. but some large integers... and the large integers (stored in a text column in excel) keep on getting changed to scientific notation. is there somewhere that i can tell windows or excel-- or something-- to NEVER EVER EVER use scientific notation? i mean seriously here is there a way to make excel WORK? because it's a crap program and i dont understand why you babies use this program. i mean-- grow up and learn a real program.. all you're doing is cramping my style. On 31 May 2005 09:57:04 -0700,...

How can stop a 9 digit number from formatting scientific
I am completing a pivot table/csv file for Government Legislation requirements and my user id number for submitting the information keeps reverting to a scientific number which is unacceptable to the government site. I have tried and better tried formatting the cell, gone into tools/edit etc but to no avail I am running out of time for submission - help! Have you tried widening the column? But I don't know what a pivot table/csv file means. Does this mean you're having trouble in the CSV file? If you're checking the CSV file by reopening it in excel, try checking ...

Currency values set to 2 decimal places
Is it possible to set a currency value fixed to 2 decimal points a opposed to just rounded up or down. for example if 33.3% discount was applied to �10 it would be �6.66666 which would display as �6.67 Therefore if you had 3 x �6.67 it woul work it out as �20.00 and not �20.01. Pau -- clappu ----------------------------------------------------------------------- clappus's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1613 View this thread: http://www.excelforum.com/showthread.php?threadid=27569 Yes, set the format to Currency, and that will work. -- HTH R...

turn off scientific notation?
I need to turn off scientific notation because when I save files it drops very relevant numbers. For instance some of my documents have tracking numbers which are up to 30 digits long it is impossible to track a package if even 1 number is wrong let a lone half of them. I realize I can change the format of the cell to number, but the thing is I'm dealing with CSV documents, a lot of them and I get more and more all the time on a regular basis. It's just too much work to have to do that on so many documents I just need this feature turned off. Especially when there are people le...

Scientific Notation
Is there a way to prevent excel from automatically converting certain numbers to scientific notation? Example-when importing data, '760985E56' automatically converts to '760985E+61'. How are you importing? If you're importing a text file, use the Text Import wizard, and in the third pane, click the field header and choose the Text radio button In article <174de01c448ee$25cffae0$a301280a@phx.gbl>, "rICH" <anonymous@discussions.microsoft.com> wrote: > Is there a way to prevent excel from automatically > converting certain numbers to sc...

excel 2003 stop rounding and decimal places
I have tried number formats of General and Number - I get slightly different results but both are not what I want. If I enter 180556 I want it to display exactly like that. I don't want it rounded to the nearest two decimal places, I don't want it truncated, etc. I want it to be a number because I need to graph the column. Any help? Thanks, John Kilgo, john.kilgo@dotnetjohn.com ...

Excel's localization of scientific notation
Does Excel use "E+" and "E-" for scientific notation for all locales? Or are there some locales for which it uses different characters? Thanks, Greg I'm pretty sure that the E is universal. If it were not, it would have been in the list of international symbols. Look here for more info about international issues: http://www.rondebruin.nl/international.htm#strings -- Kind regards, Niek Otten Microsoft MVP - Excel "Greg Lovern" <gregl@gregl.net> wrote in message news:82e5afbe-a4ba-4f5f-ac41-ac7b41519d67@h40g2000prf.googl...

change mm to decimal
If it's 22 min, I enter =22/60 to get 0.37. Is there a faster way that I can enter just 22 and it converts to 0.37 for me and I still have the option to see it in minutes if chosen? Thanks, With formulas.................. In B1 enter this formula. =IF(A1="","",A1/60) Drag/copy down as far as you wish. Cell will be blank until a number is entered in column A With event code.................. This code will add the decimal value to adjacent cell. Private Sub Worksheet_Change(ByVal Target As Range) Const myRange As String = "A1:A10&quo...

Excel defaulting to 3 decimal places when using the gen. format
I have a problem when entering numerical values in cells that have been formated to general. Somehow the cells seem to be formatted using 3 decimal places automatically even though I don't want decimal places. Entering 123 gives me a .123. Entering 1234 gives me 1.234. I believe that I may have set up a macro to automatically default all values to 3 decimal places. It is now affecting all my excel files. I can't call up the macro and delete it. Attempting to reformat the cells doesn't help. It automatically affects any new workbooks that I create. Can anyone suggest what I...

unwanted scientific notation
I populated a column by pasting in a list of account numbers. Excel displayed them all in scientific notation, obviously not useful in this situation. I formatted the column as text, but that did not change the display. But having done so, if I now double click in a cell to edit it, the display corrects itself and stays corrected even if I exit the cell with no changes. But I can't double click on each cell - there are more than a thousand of them. How can I correct the formatting of all cells at once? Paul, Try changing the formatting to Number with 0 decimal places. Note th...

sort "numbers" with decimals
I've got chapter numbers in one column which goes: 4.1, 4.2, 4.3...., 4.11, 4.12.... How can I sort it by the numbers on the right of the decimal point instead of getting: 4.1, 4.10, 4.11, 4.12, ....4.2, 4.20, 4.21, .... Hello Melissa, http://www.bplumhoff.de/software/sort_chapter.xls is a sample spreadsheet with a UDF in it which solves this. HTH, Bernd Insert two Columns at the right of the numbers. Select the range of numbers i.e. "A:A" Then go to DATA>> Text to Columns >> Delimited Uncheck all checked and then select "Other and put a "." in ...

XSLT number-format command and scientific notation (HELP!)
Well, the project I am working on has now come to a screeching halt! I have been developing a program that heavily utilizes ADO.NET record sets. To generate reports, I convert the recordset to XML, and then apply an XSLT to transform the XML into HTML. This works great (or did) until today. I just found out that the "number-format" command in XSLT can't handle scientific notation! So when I try to format these numbers I just get "NaN" on the output report!!! There are only two solutions I can see, and don't know how to implement either of them. One would be to get ...

Remove Scientific Number
I have a list of customers code need to transfer in excel sheet. and there is a problem when the customer code contain 3E001 and etc.. will automatically convert into scientific number like 3.00E+01. how can i disable the feature so that I can get the correct value after import into Excel. Either pre-format the cell as text, or prefix the value with a single quote. Jerry Edward wrote: > I have a list of customers code need to transfer in excel sheet. and there > is a problem when the customer code contain 3E001 and etc.. will > automatically convert into scientific number like 3...

Decimals
Hi , I have a pedantic client who when he exports the report to excel does not want to see two decimal places . I have set it to N0 and P0 , but alas no luck , any suggestions ? Regards Malcolm ...