Significant Figures

Background of problem:

Data the I receive is in the form of numbers stored as text. (Must keep significant figures)
I track this with a formula to return the significant digits..

The final form of the data should be stored as numbers as text. Currently using Fixed() function recalling the significant digits from the beginning.

Problem:
Ok my dilemma, the final thing I need to do is conditional formatting of the data using numbers.

Example of single data entry
Step 1 '324.24 - Received from import Step 2 Determine digits beyond decimal point =2 Step 3 Use Value() to turn into number.
324.24000 Step 4 Apply Conditional format. (if < 350 and > 300 then green text and Underline) Step 5 Reapply Significant Digits from Step 2 using Fixed(324.240000,2)
324.24 The issue is once I change the cell back to text the conditional formatting fails.

Thank you in advance.

Hi Guys and Gals,

I would like to create a simple spreadsheet that will randomly generate numbers and then count the significant figures. I think the easiest way will be to generate the base random number, multiply by 10^(rand), convert to a string, and then count from the left or the right after hitting the first non-zero digit, but I need some help with the string functions.

Column: formula
A: =randbetween(1,999) /some integer 1 to 9999
B: =randbetween(-8,8) /this will be used to move the decimal
C: = A1*10^B1 /this will be the number my students will see
D: Ltrim(str(A1)) /this will convert to a string
E: =if(c1>0,"right","left) /start counting from left or right

Now I need to know how to count the digits after the first non-zero inclusive.

Example: 102405000 Start on the right and skip the zeros, but count from the 5 to the 1 (102405), and display 6 since there are 6 sig. figs.

0.0000230800 Start on the left and skip the zeros, but count from the 2 over to the last 0, and display 6 since there are 6 sig. figs.

Any suggestions? Anybody know of any programs that already do this?

Thanks!!
Jeff Ciaccio
Sprayberry High School
Marietta, GA

In searching this site I found the following 2 formulas which I had hoped would be the solution to my problem.

=IF(A2="","",ROUND(A2,2-(1+INT(LOG10(ABS(A2))))))
=TEXT(A2,"0.0E+00")*1

Both work great in displaying an entered value of .981 as 0.98 (2 significant digits)

However, if a value of 1 is entered, I would expect these formulas to display 1.0 (2 significant digits).

I'm assuming the issue is Excel's ever-so-helpful removal of trailing zeros and can be overcome by custom formatting cells to avoid this. The problem I have is the user wants the option to vary significant digits as 2, 3 or 4. Even with custom formatting, I can't get these formulas to show more than 2 significant digits if a value of 1 [or any other whole number] is entered.

Any suggestions?

I am trying to digitize an ANSI standard that calls for specific rounding and
significant figures rules. For example, variable are specified as "7 sig
figs, simply rounded" or "4 places to right of decimal if pitch is greater
than 40, else 3 places."

I can work through the rounding, truncating, etc, but is there a way to
display these significant digits within a column using one formula? So far,
I've tried something like
"=IF(A12/$B$1<10,ROUND(A12/$B$1,6),ROUND(A12/$B$1,5))" but that won't account
for displaying. I tried conditional formatting, but that won't allow number
formats.

I want everything to be adaptable in case the main variables change (so I
can use a template sheet and copy them programmatically). Anyone have any
ideas?

Thanks,

Pflugs

Does anybody know of a special format or a rounding function that displays or rounds a number off to a specific number of significant digits.

Lets say I would like 3 significant digits this would be the results:

0.40019 -> 0.400
6.543 -> 6.54
12.543 -> 12.5
32 -> 32.0
564.7894 -> 565
54387 -> 54400 (or 5.44E+4 to be correct, but i can live with 54400)

Is there a way to apply a custom format that will only display a certain
number of significant digits?

For instance for 3 significant digits
Input Values->Display As
0.12345->0.123 or .123
1.2345->1.23
12.345->12.3
123.45->123
1234.5->1230 or 1234

I think I could make a formula that could do this, but I would rather keep
the precision. I just want to adjust the displayed precision.

Using Office 2007, "Duplicate Values" only works on identical numbers.

Is it possible to find values, which are identical to 4 significant figures, if there is a long list of data?

Many thanks!

Greetings!

I am trying to return values to a defined number of significant figures.

Bashing my brain and looking at the help I have come up with =round(Number,2-len(int(abs(Number))))

where Number is the cell reference with the number value.

However values such as 25 are returned as 25 instead of 25.0 and 0.0025 is returned as 0 instead of 0.00250.

Any help much appreciated.

Danny

Hi,

I am wondering if anyone can help me with the following statement.

I have a number (example 1.068501) which i need to round up to 3 decimal places or 4 significant figures if the number is greater than 1 so the result would be 1.069.

If the number was (example 0.926145) then we need to round up to 4 decimal places or 4 significant figures so the result would be in this example would be 0.9262.

Can someone please provide me with a formula for this sort of calculation as I have a spreadsheet of over 500 numbers!

Cheers in advance

Hi all,

I have a macro that converts data in Excel to XML format. There are certain columns in Excel format that are far too accurate than necessary and I want to reduce the number of significant digits. While this is simple in Excel itself, when I run my macro to convert to XML, the original very long number is used.

Is there any way to use the Excel formatting to, for example, use a number to 3 significant digits rather than 9, 10 etc? Or is there relatively simple VBA code to reduce the number of decimal places in ceertain cells?

Here's my challenge: I want to automate a metric conversion (ounces to grams for instance) and round the result to the number of significant figures in the original number.

Example: 23 ounces converts to 652.039 grams -- rounded to two sigfigs would be 650.

If you can help me out I'd be most appreciative.

(If this has been covered already just point me to the link)

In my code, I'm calculating a very simple ratio and have the result displayed in a message box. However, I want to have this value only show 1 or 2 significant figures (e.g. if I take 9 / 7, I want to show 1.3 and not 1.28571).

How do I do this?

If I chart x and y values using the scatter option, fit a sixth-order polynomial, display the equation and then use the equation to re-calculate y values from the x values, the answers are totally different to the original data. I think this is a precision issue, and that if I could see the coefficients to more significant figures things might improve.

Does anyone know how to alter the numbers of significant figures in displayed equations or, alternatively, how to access the coefficients independently of the displayed equation? The curve itself fits very well, so the machine is obviously able to use its own equation effectively!

Hello all!

I was wondering if someone here could help me out with a VBA problem:

I'm trying to parse a piece of data, say ( 12.3 13.0 5.5). Ideally, I'd like to "split" it and have the following data:

number(0) = 12.3
number(1) = 13.0
number(2) = 5.5

My problem is that my "number" array contains spaces (" ") which makes it impossible to parse my data into the correct cells. Due to significant figures, I can never account for the spaces. Can anyone help? It would be much appreciated!

Thanks,

sC

I have a formula that multplies a weekly hire rate by the number of days. This produces figures that have fractions of a penny that are not shown on the sheet. When I autosum these figures, it adds up all these fractions of a penny, and the resulting total does not then appear to agree.

Any suggestions for a quick fix gratefully received.

Thanks Pablo1

Hello,

I want VBA code to control the number of signficant figures in a column. In column E2:E33, values will be entered in these cells from chemical test. However, I want the values to show only 3 sig figures. (ex. 2.534 will be 2.53 or 1.986 will be 1.99. etc...) How do I do this with code?

Thanks for any help.

Hey there, I currently have a loop set up where a column in my sheet is updated with a track record number of the following format:


Row A
ALP1
ALP2
ALP3 and so on....

but what I would like to do is make it so that it appears as below:

Row A
ALP0001
ALP0002
'
'
ALP1023 and so on...

my sub I use to do this is of the form below:

Code:

Sub update_track_no()
    
Dim row As Integer
Dim column As Integer
Dim count As Integer

count = 0
pasted = False
    
    Sheets("Seperated_Values").Select
    Cells(2, 29).Select
    
    row = 2
    column = 29
    
    If (ActiveCell.Value = "" And count = 0) Then
        ActiveCell.Value = "ALP" & (row - 1)
        pasted = True
    End If
    
    Do While (ActiveCell.Value <> "")
        row = row + 1
        Cells(row, column).Select
        count = count + 1
    Loop
    
    If (count > 0 And pasted = False) Then
        ActiveCell.Value = "ALP" & (row - 1)
    End If
    
End Sub

How would I go about changing the output so that it would show as above??

Any help would be greatly appreciated.

Cheers!

Jag

Hello,
I would appreciate some help to create the VBA code for a User Defined Function that will:

1) First, divide the number in a selected cell (or range of cells) by a constant number, 100
2) Next convert the format of the results into % to two significant figures
3) And finally align the % figures in the center.

E.g. If I have the number 12.23 in a cell, I would first divide it by 100, then format it into percentage in order to get 12.23%.

Thank you.

I am having problems figuring out how to auto update my table in the spreadsheet attached named "HST". Can anyone please explain the best way to link my 2 spreadsheets together. I have attached both spreadsheets. So, I have spreadsheet 1 which is called "HST" within which there are 2 table's displaying the held stock figures for 2 site's, both on the same worksheet. I then have spreadsheet 2 which is called "Weekly Figures", within which there are 52 worksheets, each worksheet is numbered 1 to 52 (I have had to condense this to just 5 worksheets for the up-load on this site due to the file size). At the end of each week, I go into the relevant week so for e.g, this week I've updated the held stock figures for week 1 in the "weekly figures" spreadsheet. This is the figures in the 2 tables - Table 1 is column C, D & E, rows 6 to 14 and also Table 2 which is again Column C, D & E, rows Rows 19 to 27. Once I have saved the data entered in the 2 tables within week 1 of the "Weekly Figures" spreadsheet, I would then like the data to display in the 2 tables within Spreadsheet 1 "HST" the next time the spreadsheet is opened. What would be the best way to do this.? Then the following week, I will then complete worksheet 2 within the "weekly figures" spreadsheet, with that weeks figures, after which, I would then like week 1 figures in the HST spreadsheet to overwite with week 2 figures the next time its opened. Does that make sense..?? I've attached both spreadsheets, if any one can help it would be appreciated & it would save me alot of work as this would be a function I would use in many other spreadsheets I have at work. Many Thanks Laura

Hi everybody!

I've made a macro in Excel/VBA, which I use to format numbers in Excel 2003 in such a way that the number is formatted with the number of decimals corresponding to a defined number of significant digits. For example: for 2 significant digits, the numbers 1234.56 and 0.56789 are formatted to 1200 and 0.57, respectively. It's important to stress that only the number format is changed, and not the values themselves, i.e. the values are not rounded down.

(And yes: I know that Excel 2007 can do this, but my workplace is stuck with 2003 for at least another couple of years... )

So far, so good... But what I want is a user-defined Excel function that does the same trick. For example, if my value is in cell A1, then I'd like to write in cell B1:

=USERFUNCTION(A1,SIGDIG)

Where SIGDIG is the chosen number of significant digits. The value of B1 will be the same as A1, but with the right number format.

I've enclosed my macro code - if there's anyone out there that can help turn this macro into a user-defined Excel programme, I'd be eternally grateful.

Regards, Matthew A. Rasmussen

'==============================================================
Sub FormatSigDig()

Dim value As Double
Dim decimals As Integer

value = Selection.value
significantdigits = 2

If value >= 9.95 Then
decimals = 0

Else: decimals = significantdigits - 1 - Int(Math.Log(Abs(Round(value, significantdigits - 1 - Int(Math.Log(Abs(value)) / Math.Log(10))))) / Math.Log(10))

End If

Select Case decimals
Case 0
Selection.NumberFormat = "0"
Case 1
Selection.NumberFormat = "0.0"
Case 2
Selection.NumberFormat = "0.00"
Case 3
Selection.NumberFormat = "0.000"
Case 4
Selection.NumberFormat = "0.0000"
Case 5
Selection.NumberFormat = "0.00000"
Case 6
Selection.NumberFormat = "0.000000"
Case 7
Selection.NumberFormat = "0.0000000"
Case Is > 7
Selection.NumberFormat = "0.0E+00"
End Select

End Sub
'==============================================================