significant figures problem

[BAlGaInTl]

Hello all...

I'm using a formula that I found elsewhere on the board to rount a number based on significant figures. This is a sample of what I'm using.

ROUND(G32,B11-1-INT(LOG10(ABS(G32))))

Where G32 is the number you want to round, and B11 is the number of sig. figs. desired.

Here is the problem that I have found. It seems that Excel insists on dropping the last number if it is a zero. So, in effect, what you get is not correct if the last significant figure is a zero.

Example. Lets say that I want 2 significant figures.

0.00123 will correctly display as 0.0012

However,

0.00101 will display as 0.001, which is only 1 significant figure, and not 2.

This only happens when the last number is a zero.

Help?

 

[bosco_yip]

....................................

=IF(B8-1-INT(LOG10(ABS(A8)))<=0,ROUND(A8,B8-1-INT(LOG10(ABS(A8)))),IF(ISERROR(TEXT(A8, "#,##0." & REPT(0,B8-1-INT(LOG10(ABS(A8)))))),FIXED(A8,B8-INT(LOG10(ABS(A8)))-1),TEXT(A8, "#,##0." & REPT(0,B8-1-INT(LOG10(ABS(A8)))))))

....................................

ray:

Sdible,

A more simple significant figures solution, the formula enter in D3 :

=FIXED($A3,$B3-INT(LOG10(ABS($A3)))-1)

The formula haven't be fully tested, but achieve the same results in the below datas.

This is shown below:

Book1
	A	B	C	D	E
1			Modified	Simplified
2	Num	SigFigs	formula	formula
3	0.000123	1	0.0001	0.0001
4	0.000123	2	0.00012	0.00012
5	0.000123	3	0.000123	0.000123
6	0.000123	4	0.0001230	0.0001230
7	0.00123	1	0.001	0.001
8	0.00123	2	0.0012	0.0012
9	0.00123	3	0.00123	0.00123
10	0.00123	4	0.001230	0.001230
11	0.1234	1	0.1	0.1
12	0.1234	2	0.12	0.12
13	0.1234	3	0.123	0.123
14	0.1234	4	0.1234	0.1234
15	12.345	1	10	10
16	12.345	2	12	12
17	12.345	3	12.3	12.3
18	12.345	4	12.35	12.35
19	128.567	1	100	100
20	128.567	2	130	130
21	128.567	3	129	129
22	128.567	4	128.6	128.6
23	1234.5	1	1000	1,000
24	1234.5	2	1200	1,200
25	1234.5	3	1230	1,230
26	1234.5	4	1235	1,235
27
Sheet1

 

[Mike Collett]

Thank you very much bosco-yip

I am currently working on converting 50,000 imperial volume measures to metric units, and rounding the result by 4 significant figures.

Your shortened formula: =FIXED($A3,$B3-INT(LOG10(ABS($A3)))-1) works on my test numters.

However, I noticed that when the values, which are produced from the formual are summed in column - i.e. using Excel's SUM function the result is 0.

Any idea why this would happen? And how to solve it?

My real concern, however, is I need to multiply the result of your formula in order to produce a rate (i.e. thousands of rounded metric conversions multiplied by a number to produce rate).

Thankfully, mulitplication seems to work

Any comments which you might have would be greatly appreciated.

Thank you

 

[bosco_yip]

Book1
	A	B	C	D
1	imperial	metric	ratewith
2	volume	volume	multipliednumber
3	ft3	m3	5.00
4	123,545.00	3,498	17,490.00
5	10,000.00	283.1	1,415.50
6	320.00	9.060	45.30
7	Total:	3,790.16
8
Sheet1

Mike Collett,

Q.1) Regarding converting imperial volume measures to metric units, and rounding the result by 4 significant figures.

Enter formula in Cell B4 :

=FIXED($A4/35.31984,4-INT(LOG10(ABS($A4/35.31984)))-1)

The conversion is based on :
1) Formula : =FIXED(Num,SigFig-INT(LOG10(ABS(Num)))-1)
2) 1 m3 = 35.31984 ft3
3) Significant figures (SigFig) = 4


Q.2) To multiply the result of formula in order to produce a rate

In C3 house the multiplied number

Enter formula in Cell C4 :

=B4*$C$3

Q.3) SUM function the significant figuresc results

In B7 enter array formula ( Shift+Ctrl+Enter) :

=SUM(VALUE(B4:B6))


Hope can helps

Regards
Bosco

 

[Mike Collett]

Thank you bosco_yip

I am very grateful for your response

Unfortunately, as a novice to Mr.Excel, I have two message boards responding to the same problem.

This was a dumb thing to do - my apology

Please take a look at:

www.mrexcel.com/board2/viewtopic.php?p=367854

Thank you again

 

[BAlGaInTl]

Bosco_yip

Your solution is much shorter and effective for sure.

For anyone interested, I have made a couple of modifications for people that I work with.

The first is the ability to change from SigFigs to Decimal precision. I achieved this by having the user use a negative sig fig instead of a positive one. So, by entering -2, 12.345 would be rounded to 12.35.

=IF(B25>=0,FIXED(A25,B25-INT(LOG10(ABS(A25)))-1),ROUND(A25,ABS(B25)))

Where B25 is the number of sigfigs and A25 is the number to be rounded.

Of course, if the user wants to force trailing zeros, the ROUND statement can be changed to FIXED.

The second is to use SigFigs, but with a limiter to the maximum number of decimal places that can be used. In this case if a user wants 3 sigfigs and no more than 4 decimal places they could use the formula

=IF(C37<B37-INT(LOG10(ABS(A37)))-1,ROUND(A37,C37),FIXED(A37,B37-INT(LOG10(ABS(A37)))-1))

Where C37 is the "maximum decimal precision" desired, B37 is the SigFigs desired, and A37 is the number to be rounded. Using this formula for the number 0.001268 to 3 sig figs with a maximum decimal precision of 4 would result in 0.0013 returned. Again, if trailing zeros are desired when MDP is used, the ROUND statement can be changed to FIXED.

Just in case anyone can use these. I may rewrite my function to be used in the following manner

=SigFig(Number,SigFigs,[MDP])

where the MDP would be optional.