Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

Significant Figures

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hello everyone.

We get lab data reports which have data in the form of

"<0.00657," or 4.2, 300, etc.

How do I create a spreadsheet which will automatically count the number of non- zero digits and display the correct significant figures?

Thanks!

Penny


Similar Excel Video Tutorials

Helpful Excel Macros

Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Count The Number of Words in a Cell or Range of Cells in Excel - UDF
- Count words in cells with this user defined function (UDF). This UDF allows you to count the number of words that are w
Filter Data to Show the Bottom X Percent of the Data Set in Excel - AutoFilter
- This free Excel macro filters data to display the bottom X percent of the data set in Excel. This is a great macro to u
Create a Line Chart with a Macro in Excel
- Create a line chart in Excel with this macro. This allows you to quickly and easily create line charts and graphs from
Get Values from a Chart
- This macro will pull the values from a chart in excel and list those values on another spreadsheet. This will get the s

Similar Topics







I tried searching the subject to no avail.

Is there a worksheet function similar to round that will always round but outputs a specified amount of significant figures.

Example:
Cell A1 value = 0.0111
Cell A2 value = 0.00222

Both cells have 3 significant figures and I only need to display 2 significant figures so in theory they should display with rounding:

Cell A1: 0.011
Cell A2: 0.0022

If I use the function Round(A1,3) then Cell A1 displays 0.011 which is correct however if the value of A1 ever changes to 0.00333 or 0.00111 etc. then cell A1 will display the incorrect number of sig figs unless i changed the formula to Round(A1,4) but I don't want to be doing that all the time.

Hope this makes sense.


Working scientific field here and have a requirement to "report the result to 3 significant figures".

Our result is a mean [avg] and it will normally range from .05 to .199.

The quandry is how to format the cell to show "3 significant figures".

For example, the value ".0500" is considered to have 3 significant figures by this client's definition. Which would mean I have to format to at least 4 decimal places. However, if the calculated mean value is .1234, I'm only supposed to show 3 decimal places.

What's a programmer to do? Anyone else face this issue before?


I seem to have hit a limit of 16 significant digits. For example:
1E+15 + 1 = 1000000000000001 (16 significant digits)
(note that 1000000000000000 is displayed but the 1's place value is still
retained)
whereas
1E+16 + 1 = 10000000000000000.
The loss of significant digits can be verified by resubtracting the the
initial large number, e.i.:
1E+15 + 1 - 1+E15 = 1, whereas
1E+16 + 1 - 1+E16 = 0.
Is there a way of getting past this limitation? I need more than 40
significant digits for some rather special calculations. I have created a
workaround which cuts numbers into up to 3 15-significant-digit pieces, but
it's inelegant and requires very long formulas. Thanks.



Can excel cells be defined as a significant figure.

eg a cell containing the caluclation eg 55/3

= 18.333333333

i want that cell only to display 2 significant figures in this case 18

or if the calculation happened to give you an answer of 0.2458955435

then 2 signifcant figures would be 0.2

Thanks


Can i set a range of cells to 3 significant figures automatically? I can only find it possible to do decimals places. Any ideas?

Thanks


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.



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


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!


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)




I need to know how to round to significant figures using excel, its for a question in one of my college assignments and i cant seem to find any information.. thankyou!

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.


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.


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