Can Excel 97 clculate significant figure 7

[kelqc]

Hi

I have some data that I neeed to apply decimal places and significant figure parameters too in Excel 97

eg 121.2365 becomes 121.2 (4 sig figures, 1 decimal place)

I am sure I can do the decimal places in Excel, but I am not aware of any function to do significant figures.

Does anybody have any ideas?

Thanks Kelvin

 

[foundryqa]

Kevin,

You can use the Round function in vba and instead of leaving off the optional arguement for places to the right of the decimal, just fill in that value with the number of significant digits you want. For more information on the function, type in round function into excel help.

Fred

 

[TonyJollans]

Hi Kelvin,

There doesn't seem to be any native way, or even any easy way to do this, but I think this will work.

With your original number in A1, put this in B1 ..

[blue][tt]=ROUND(A1,4-(MAX(INT(LOG10(ABS(A1)))+1,0)))[/tt][/blue]

I can't help thinking there should be an easier way.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[TonyJollans]

Sorry, forgot to point out the formula gives 4 significant figures because of the highlighted 4 below; change that for a different significance ..

[blue][tt]=ROUND(A1,[highlight]4[/highlight]-(MAX(INT(LOG10(ABS(A1)))+1,0)))[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[foundryqa]

Apologies Kevin,

I reread your significant digit question and relize that you need something more than the round function.

=VALUE(LEFT(A2,5))

Where 5 is the amount of digits you want seems to work for me.

Fred

 

[TonyJollans]

Hi Kelvin,

Just looked back at my post after reading Fred's and realised I should have taken the Max out ...

[blue][tt]=ROUND(A1,4-INT(LOG10(ABS(A1)))+1)[/tt][/blue]

Fred,

Your formula won't work with, for example, 12123 (giving 12123 instead of 12120) or 0.12123 (giving 0.121 instead of 0.1212), or even 121.2865 (giving 121.2 instead of 121.3)

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[foundryqa]

Tony,

That's two strikes for me, I better let you have this one. At this rate it will be a matter of minutes before I strike out. You are exactly right.

Fred

 

[TonyJollans]

Hi Fred,

My record ain't that good on this one either

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[kelqc]

Hi Fred and Tony

Thanks for all your help guys, I new it wasn't going to be easy. I will sit down tonight and try Tony's formula out. You both deserve a star.

Kelvin

 

[kelqc]

Hi Tony

I just tried out your formulae, the one with the MAX in it has worked fine, but the one without the MAX didn't work as expected.

Also, if possible, can you explain what each section of the formula does?, I am pushing my luck or what.


Many thanks

Kelvin

 

[TonyJollans]

Hi Kelvin,

As no-one has come up with anything better, I'm happy to explain.

To get a number to n significant figures is simply a rounding exercise, hence the ROUND function; this gives us ..

[blue][tt] ROUND(A1, ...)[/tt][/blue]

The ... needs to be a number. Normally people use rounding to round digits after the decimal point, e.g.

[tt] ROUND(0.456,2)[/tt] rounds to 2 decimal places (the nearest multiple of 10 ^ -2), giving 0.46
[tt] ROUND(0.456,1)[/tt] rounds to 1 decimal place (the nearest multiple of 10 ^ -1), giving 0.4

It is also possible to use negative numbers to round before the decimal point, e.g.

[tt] ROUND(456,[red]-[/red]2)[/tt] rounds to 2 decimal places (the nearest multiple of 10 ^ -(-2)), giving 500

To round to a particular significance depending on the number (i.e n significant figures) we need to find out how many significant digits there are to start with in order to feed the right number into the ROUND Function. For this we use logarithms (the LOG10 Function).

A brief reminder from your schooldays: the logarithm of a (positive) number consists of two parts; the integer part which is the largest power of 10 lower than the number, and the decimal part indicating where the number falls in the range between that and the next highest power of 10. The integer part is what we are interested in here because it tells us where the first significant digit is, and for this we use the INT Function. An examples ..

[tt] LOG10(456) [/tt] = 2.65896, and ..
[tt] INT(LOG10(456))[/tt] = 2, telling us that the number falls between 10 ^ 2 and 10 ^ 3

It is slightly more complex for numbers less than 1; the logarithm of 0.0456 is (mathematically) -2 +0.65896 and the integer part of this is -2. This doesn't appear quite so obviously from an example, but the INT Function moves away from zero so the result is what we want.

[tt] LOG10([red]0.0[/red]456) [/tt] = -1.34104, and ..
[tt] INT(LOG10([red]0.0[/red]456))[/tt] = -2, telling us that the number falls between 10 ^ -2 and 10 ^ -1

Adding 1 to the integer part of the log gives us the number of digits before the decimal point so, with the above examples ..

[tt] INT(LOG10(456))[/tt] = 2; add 1 and we find there are 3 digits before the point
[tt] INT(LOG10([red]0.0[/red]456))[/tt] = -2; add 1 and we find there are -1 digits before the point (in other words there +1 zeroes after the point before we get to significant digits)

Back to the ROUND Function. Rounding to n significant figures means rounding to the position n digits after the first significant figure (which is the number of digits before the point which we got from the Log above), in other words ..

[tt] INT(LOG10(number)+1) - n[/tt]

Because the ROUND Function requires the negative of this, what we actually feed into it is the reverse of the above ..

[tt] n - (INT(LOG10(A1))+1)[/tt]

(where A1 is the cell containing the original number)

Putting it together, and using 4 significant figures as example, we get ..

[tt] ROUND(A1,4-(INT(LOG10(A1))+1))[/tt]

Finally, to cater for negative numbers, we ignore the sign in the process of determining the length. This is achieved by using the ABS Function, which gives the magnitude (or absolute value) of a number. Adding this into the mix gives the final formula of ..

[tt] ROUND(A1,4-(INT(LOG10(ABS(A1)))+1))[/tt]

Finally, the MAX Function. I left this in by mistake and am surprised you find it works with it but not without. It only has effect for very small numbers, as per this example

[tt] ROUND(A1,4-(INT(LOG10(ABS(0.00045678)))+1,0))[/tt] should give 0.0004568 (i.e. 4 significant figures after all the zeroes after the decimal point), and ..
[tt] ROUND(A1,4-(MAX(INT(LOG10(ABS(A1)))+1,0)))[/tt] should give 0.0005 (i.e. 4 digits after the decimal point regardless of significance)

Sorry about the rather long post. It looks like I got carried away. I hope it helps and, an absolute final note, I hope I've got all the closing parentheses in the right places

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[Nikita6003]

Tony,

that's a truly excellent explanation - it took my math teacher a lot more time to explain this to me AND I still didn't get it
Star from me & please feel free to get carried away more often ;-)


Cheers
Nikki
Look, mommy, I'm flying!

 

[foundryqa]

You impressed the heck out of me Tony. I like it. You deserve a star for that one. Do I see an FAQ coming out of this?

 

[KenWright]

Star from me Tony, because I think a logical explanation like that always helps numerous people that would really have liked one, but just never ask. Those that understand it can skip through it, but some of those that don't will see it as golden.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[TonyJollans]

Hi All,

Thanks for all the stars. I have now written this up as a FAQ in the Microsoft: Office Forum (faq68-5066).

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[kelqc]

Hi Tony,

Thanks for the excellent explanation, it has taken me a while, but I get it. As a scientist I like to break down and understand the formulae I use. It helps me to apply them in other areas sometimes.

Regards

Kelvin

 

[Bowers74]

Being that this is the VBA forum, I just figured that I would add a function (based on Tony's post and FAQ), just in case anyone in interested and would like to add it to thier PERSONAL.xls file.

Function SIGNUM(Number As Double, Significance As Integer) As Double
Dim DecimalPlaces As Integer
DecimalPlaces = Significance - Int((Log(Abs(Number)) / Log(10#)) + 1)
SIGNUM = Application.WorksheetFunction.Round(Number, DecimalPlaces)
End Function

Assuming A1 contains 1234.56789


=SIGNUM(A1,2) ' returns 1200
=SIGNUM(A1,4) ' returns 1235
=SIGNUM(A1,6) ' returns 1234.56

Assuming A1 contains 0.045678

=SIGNUM(A1,2) ' returns 0.046
=SIGNUM(A1,4) ' returns 0.04568
=SIGNUM(A1,6) ' returns 0.0456789

[Cheers] It 's Friday after all!!!

Tony,
You can add this to your FAQ (great by the way) if you want. P.S. Star from me too! ;-)


Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884