How To Change Sig Figs On Excel

Warning: crazy-long excel formula ahead

I was as well looking to piece of work with pregnant figures and I was unable to use VBA as the spreadsheets can't support them. I went to this question/answer and many other sites but all the answers don't seem to deal with all numbers all the time. I was interested in the accepted answer and it got close but equally soon as my numbers were < 0.one I got a #value! error. I'm sure I could take stock-still it simply I was already down a path and merely pressed on.

Problem:

I needed to study a variable number of significant figures in positive and negative mode with numbers from x^-5 to 10^v. Also, according to the client (and to regal math), if a value of 100 was supplied and was accurate to +/- 1 and we wish to present with iii sig figs the answer should be '100.' so I included that likewise.

Solution:

My solution is for an excel formula that returns the text value with required significant figures for positive and negative numbers.

It's long, but appears to generate the right results according to my testing (outlined beneath) regardless of number and significant figures requested. I'm certain information technology can be simplified but that isn't currently in scope. If anyone wants to suggest a simplification, please exit me a comment!

          =TEXT(IF(A1<0,"-","")&LEFT(TEXT(ABS(A1),"0."&REPT("0",sigfigs-i)&"Due east+00"),sigfigs+1)*10^FLOOR(LOG10(TEXT(ABS(A1),"0."&REPT("0",sigfigs-one)&"E+00")),1),(""&(IF(OR(AND(Flooring(LOG10(TEXT(ABS(A1),"0."&REPT("0",sigfigs-ane)&"Eastward+00")),one)+1=sigfigs,RIGHT(LEFT(TEXT(ABS(A1),"0."&REPT("0",sigfigs-1)&"E+00"),sigfigs+1)*x^Flooring(LOG10(TEXT(ABS(A1),"0."&REPT("0",sigfigs-i)&"E+00")),1),1)="0"),LOG10(TEXT(ABS(A1),"0."&REPT("0",sigfigs-1)&"E+00"))<=sigfigs-1),"0.","#")&REPT("0",IF(sigfigs-1-(FLOOR(LOG10(TEXT(ABS(A1),"0."&REPT("0",sigfigs-ane)&"E+00")),1))>0,sigfigs-i-(FLOOR(LOG10(TEXT(ABS(A1),"0."&REPT("0",sigfigs-1)&"Due east+00")),i)),0)))))                  

Note: I have a named range called "sigfigs" and my numbers start in cell A1

Test Results:

I've tested it confronting the wikipedia list of examples and my own examples and then far in positive and negative. I've besides tested with a few values that gave me issues early on and all seem to produce the right results.

I've too tested with a few values that gave me issues early and all seem to produce the correct results now.

3 Sig Figs Test

          99.99 -> 100. 99.9 -> 99.9 100 -> 100. 101 -> 101                  

Notes:

Treating Negative Numbers

To Treat Negative Numbers, I have included a concatenation with a negative sign if less than 0 and use the absolute value for all other piece of work.

Method of construction: It was initially divided into most 6 columns in excel that performed the various steps and at the end I merged all of the steps into one formula above.

Source: https://stackoverflow.com/questions/20645757/how-to-get-excel-to-display-a-certain-number-of-significant-figures

Posted by: woodsfambireett.blogspot.com

Share this post