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

Create Name Formulas
- See how to create name formulas that can be then used in cells.

1) See how to create a dynamic range using a named formula that uses the OF ...

Simple & Compound Interest
- See how to use the FV function. See the math formula for calculating Future Value and for calculating the effective interest rate. Also see long hand ...

## Helpful Excel Macros

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.

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

'==============================================================