Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
significant digits
Is there a format available to display significant digits? If you have the
following data: 0.99 1.0 1.1 1.11 is there a format you can use to display say 2 significant digits for the entire data set? Two significant digit display would be: 1.0 1.0 1.1 1.1 Is there a formula that will use only X significant digits in the calculation? We are required to report to regulatory agencies using significant digits rather than decimal places. Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
significant digits
How are your significant digits defined? I would just translate that
into decimals, the example you gave is 1 decimal place. So you can use ROUND for a formula or format cells to "0.0". |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
significant digits
Significant digits are different from decimal places, in that you are only
looking at a set number of significant placeholders rather than looking at a set decimal point. ..1 .10 1 1.0 12 12 123 120 1234 1200 etc. The problem with the ROUND function is that it always rounds either up or down. Again, our rules are different. We must round even up, odd down or odd up even down. This reduces bias from the rounding process. "Spiky" wrote: How are your significant digits defined? I would just translate that into decimals, the example you gave is 1 decimal place. So you can use ROUND for a formula or format cells to "0.0". |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
significant digits
You can use IF combined with ROUNDUP/DOWN and probably ISEVEN/ISODD
for the formulas. I do know what "significant digits" means. What I asked is, "How are YOUR significant digits defined". If you have a set method of selecting the significant digits, like typing a "2" into a cell, perhaps you can find a method of instructing Excel what to do. There are also places to find help via Google. Ex: http://www.vertex42.com/ExcelTips/si...t-figures.html But I have to ask....you find more bias based on =5 than on even/odd rounding? I'd like to see the math on that. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
significant digits
Spiky,
We use Excel to generate regulatory reports. The digits in the reports must be to 2 significant digits. The problem is in the formatting. No matter what you enter, what you see reverts back to the formatting. So if you enter 1.0 and the formatting is set to .00, you will see 1.00. The agency does not like that. I could change the formatting every time, for each data point, but that is asking for an error. Is there something automatic that will work with the formatting? It looks like the second (huge!) formula on the webpage you pointed me to will work, but I couldnt figure out the inputs. Can you give me the 5th grader explanation? Rounding€¦ I wish we could just use the <=5 standard rounding. I fought it and lost. When all is said and done, you must satisfy the boss. Thanks for your time and help! "Spiky" wrote: You can use IF combined with ROUNDUP/DOWN and probably ISEVEN/ISODD for the formulas. I do know what "significant digits" means. What I asked is, "How are YOUR significant digits defined". If you have a set method of selecting the significant digits, like typing a "2" into a cell, perhaps you can find a method of instructing Excel what to do. There are also places to find help via Google. Ex: http://www.vertex42.com/ExcelTips/si...t-figures.html But I have to ask....you find more bias based on =5 than on even/odd rounding? I'd like to see the math on that. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
significant digits
It looks like the second (huge!) formula on the webpage you pointed me to will work, but I couldn’t figure out the inputs. Can you give me the 5th grader explanation? You should be able to change all the instances of "value" to reference your number. So whatever cell that is. And change all of the "sigfigs" to reference how many digits are needed. If it is always 2, just change these to "2" (with no quotes). If it might change, I'd put the "2" at the top of your page in a cell and reference that cell in the big formula. Looks like 4 instances of each. Also, it probably won't copy very well. I tried it out and it pasted into 3 cells, so you have to cut and paste to get it into one formula. But it appears to work just fine. If I could remember all the LOG stuff I once knew, I still don't think that could be explained at a 5th grade level. ;-) Oh, this returns Text. If you want to actually use these numbers in a later calculation, wrap a VALUE around it. Just add "VALUE(" at the beginning and one more parenthesis at the end. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
significant digits
Hello Gordon,
=--TEXT(A1,"0.0E+0") Note that 0.99 presented with 2 significant digits still is 0.99. Leading zeros dont count. Regards, Bernd |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
significant digits
Excellent, Bernd P! A simple and elegant solution!
Spiky, thanks again for your help. "Bernd P" wrote: Hello Gordon, =--TEXT(A1,"0.0E+0") Note that 0.99 presented with 2 significant digits still is 0.99. Leading zeros dont count. Regards, Bernd |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
significant digits
Bernd P, if I enter 1.0 or 1.00, it only shows as 1, which to the regulators
is just as wrong as 1.00. "Gordon" wrote: Excellent, Bernd P! A simple and elegant solution! Spiky, thanks again for your help. "Bernd P" wrote: Hello Gordon, =--TEXT(A1,"0.0E+0") Note that 0.99 presented with 2 significant digits still is 0.99. Leading zeros dont count. Regards, Bernd |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
significant digits
On Tue, 23 Sep 2008 08:35:01 -0700, Gordon
wrote: Is there a format available to display significant digits? If you have the following data: 0.99 1.0 1.1 1.11 is there a format you can use to display say 2 significant digits for the entire data set? Two significant digit display would be: 1.0 1.0 1.1 1.1 Is there a formula that will use only X significant digits in the calculation? We are required to report to regulatory agencies using significant digits rather than decimal places. Thanks! What are you using for a definition of "significant digits"? I ask because your first example shows 0.99 -- 1.0 and the definitions of which I am aware only count zeros as significant if they occur between two non-zero digits; or if they are trailing zeros in a number with a decimal point. --ron |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
significant digits
Ron,
You are correct. 0.99 is two significant digits. My mistake. "Ron Rosenfeld" wrote: On Tue, 23 Sep 2008 08:35:01 -0700, Gordon wrote: Is there a format available to display significant digits? If you have the following data: 0.99 1.0 1.1 1.11 is there a format you can use to display say 2 significant digits for the entire data set? Two significant digit display would be: 1.0 1.0 1.1 1.1 Is there a formula that will use only X significant digits in the calculation? We are required to report to regulatory agencies using significant digits rather than decimal places. Thanks! What are you using for a definition of "significant digits"? I ask because your first example shows 0.99 -- 1.0 and the definitions of which I am aware only count zeros as significant if they occur between two non-zero digits; or if they are trailing zeros in a number with a decimal point. --ron |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
significant digits
On Thu, 25 Sep 2008 06:07:00 -0700, Gordon
wrote: Ron, You are correct. 0.99 is two significant digits. My mistake. "Ron Rosenfeld" wrote: Well, you are going to have to return a string in order to retain the required trailing zero's. Here is a User Defined Function that will do that. To enter this, <alt-F11 opens the VB Editor. Ensure your project is highlighted in the Project Explorer window, then Insert/Module and paste the code below into the window that opens. To use this, enter the formula =RSD(cell_ref, numSigDigits) into some cell. e.g. =RSD(A1,2) for 2 significant digits. ============================== Option Explicit Function RSD(n As Double, SigDigits As Integer) As String 'outputs a string right padded with zeros to SigDigits Dim fmt As String Dim Sign As Integer Dim ZerosLeft As Integer, ZerosRight As Integer, ExtraZeros As Integer Sign = Sgn(n) n = Abs(n) RSD = Application.WorksheetFunction.Round _ (n, Fix(-Log(n) / Log(10)) + SigDigits - 1) Debug.Print n, RSD ZerosLeft = InStr(RSD, ".") - 1 If ZerosLeft = -1 Then ZerosLeft = Len(RSD) ZerosRight = Len(RSD) - ZerosLeft - 1 If Fix(RSD) = 0 Then ZerosLeft = 0 fmt = "0" End If ExtraZeros = SigDigits - (ZerosLeft + ZerosRight) fmt = fmt & WorksheetFunction.Rept("0", ZerosLeft) If (ZerosRight + ExtraZeros 0) Then fmt = fmt & "." fmt = fmt & WorksheetFunction.Rept("0", ZerosRight + ExtraZeros) End If RSD = Format(Val(Sign * RSD), fmt) End Function ========================= --ron |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
significant digits
On Thu, 25 Sep 2008 06:07:00 -0700, Gordon
wrote: Ron, You are correct. 0.99 is two significant digits. My mistake. The text formula on the page that Spiky referred you two might be more convenient. I just noted that and it seems to work properly, also. --ron |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
significant digits
Hello Gordon,
Then wrap another TEXT function around it: =TEXT(--TEXT(A1,"0.0E+0"),"0.00") Or format the cell accordingly. Regards, Bernd |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
significant digits
Bernd P's formula seems to work well:
=TEXT(--TEXT(A1,"0.0E+0"),"0.00") It hasn't failed me yet. Thanks to all, what a great resource! |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
significant digits
Hi Ron,
Your UDF returns for RSD(0.99,2)=1.0 My UDF Dbl2NSig results in 0.99: http://www.sulprobil.com/html/dbl2nsig.html Regards, Bernd |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
significant digits
Bernd P wrote...
Then wrap another TEXT function around it: =TEXT(--TEXT(A1,"0.0E+0"),"0.00") Or format the cell accordingly. FWIW, this could mishandle numbers between 0 and 0.1, e.g., 0.093 should remain 0.093 rather than becoming 0.1. If the value were x and the number of significant digits n, to be exhaustive you'd need something like =IF(x<0,"-","")&TEXT(--TEXT(ABS(x),"."&REPT(0,n)&"E+0"), "."&REPT(0,MAX(0,IF(ABS(x)<1,1,n-1)-INT(LOG10(ABS(x)))))) |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
significant digits
On Mon, 29 Sep 2008 12:50:00 -0700, Gordon
wrote: Bernd P's formula seems to work well: =TEXT(--TEXT(A1,"0.0E+0"),"0.00") It hasn't failed me yet. Thanks to all, what a great resource! It doesn't seem to work properly here. 1.234 -- 1.20 Two sig digits should be 1.2 Three sig digits should be 1.23 --ron |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
significant digits
On Mon, 29 Sep 2008 15:28:43 -0700 (PDT), Bernd P wrote:
Hi Ron, Your UDF returns for RSD(0.99,2)=1.0 My UDF Dbl2NSig results in 0.99: http://www.sulprobil.com/html/dbl2nsig.html Regards, Bernd Your correct. I posted in error. WRT your UDF, why not modify it so it also handles negative numbers? =dbl2nsig(-0.99,3) -- "0.-.9" --ron |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
significant digits
Hello Harlan,
Right. --TEXT(A1,"0.0E+0") calculates the 2 most significant digits, and the rest was IMHO presentational guess work. Regards, Bernd |
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
significant digits
Hi Ron,
Thanks. You are right. This should do the trick: Function nsig(d As Double, l As Long) As Double 'Returns double with l most significant digits of d. Dim s As String s = "0." & String(l - 1, "0") & "E+0" nsig = Format(d, s) End Function Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can I sum up the values with more than 15 significant digits? | Excel Discussion (Misc queries) | |||
Format significant digits | Excel Discussion (Misc queries) | |||
Significant digits | Excel Worksheet Functions | |||
significant digits for decimals | Excel Worksheet Functions | |||
How do I increase the of significant digits given in the slope i. | Charts and Charting in Excel |