Scientific to Decimal
in [Visual Basic]
|
From: Rick Rothstein on 1 Dec 2009 15:52 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(a)noway.com> wrote in message news:bttah5hon83ti9i7qrsudkr5or3mh8qrno(a)4ax.com... > On Tue, 1 Dec 2009 03:55:54 -0500, "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? > > 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
From: Webbiz on 1 Dec 2009 16:20 On Tue, 1 Dec 2009 15:26:45 -0500, "Jeff Johnson" <i.get(a)enough.spam> wrote: >"Webbiz" <nospam(a)noway.com> wrote in message >news:bttah5hon83ti9i7qrsudkr5or3mh8qrno(a)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
From: Webbiz on 1 Dec 2009 16:22 On Tue, 1 Dec 2009 15:52:07 -0500, "Rick Rothstein" <rick.newsNO.SPAM(a)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
From: Webbiz on 1 Dec 2009 16:30 On Tue, 1 Dec 2009 15:52:07 -0500, "Rick Rothstein" <rick.newsNO.SPAM(a)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
From: Rick Rothstein on 1 Dec 2009 16:49
>>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) |