significant figures
Author Message
significant figures

Does anyone out there have a function that will round values in Excel 97 to
a specified number of significant figures?  Thanks.

Thu, 08 Jul 2004 08:15:49 GMT
significant figures
In the worksheet, use the built-in

=ROUND(A1, n)

where   n<16.  If n is negative, it will round to 10s, hundreds, etc.

If you really want a programming solution, you can use the same
function:

myVal = Application.Round(Range("A1"), numFigs)

Quote:
> Does anyone out there have a function that will round values in Excel 97 to
> a specified number of significant figures?  Thanks.

--

Thu, 08 Jul 2004 08:26:16 GMT
significant figures
=ROUND(A1,3-INT(LOG10(ABS(A1)))-1)

The 3 is the number of digits to round to.

Jon

Quote:

> Does anyone out there have a function that will round values in Excel 97 to
> a specified number of significant figures?  Thanks.

Thu, 08 Jul 2004 08:32:03 GMT
significant figures
Thanks, Jon.  This looks like what I need.  However, I'm having a problem
when I try to use this formula in VBA code.  It chokes on both the Round and
Log10 functions and gives me a "Sub or function not defined" error even
though these functions are supposed to be available to Visual Basic.  I'm
unable to find a Knowledge Base article that addresses this.  Any ideas?  Do
I need to set some obscure reference that I'm not aware of?  Thanks.

Amy

Quote:
> =ROUND(A1,3-INT(LOG10(ABS(A1)))-1)

> The 3 is the number of digits to round to.

> Jon

> > Does anyone out there have a function that will round values in Excel 97
to
> > a specified number of significant figures?  Thanks.

Thu, 08 Jul 2004 14:45:35 GMT
significant figures
Well Amy, you did ask for a function in Excel 97, not VBA. Try this:

Function SigFig(dblValue As Double, lngHowMany As Long) As Double
Application.Volatile
Dim lngDigs As Long
lngDigs = lngHowMany - Int(Log(Abs(dblValue)) / Log(10)) - 1
SigFig = Int(0.5 + dblValue * 10 ^ lngDigs) / 10 ^ lngDigs
End Function

HTH. Best wishes Harald

Quote:
> Thanks, Jon.  This looks like what I need.  However, I'm having a problem
> when I try to use this formula in VBA code.  It chokes on both the Round
and
> Log10 functions and gives me a "Sub or function not defined" error even
> though these functions are supposed to be available to Visual Basic.  I'm
> unable to find a Knowledge Base article that addresses this.  Any ideas?
Do
> I need to set some obscure reference that I'm not aware of?  Thanks.

> Amy

> > =ROUND(A1,3-INT(LOG10(ABS(A1)))-1)

> > The 3 is the number of digits to round to.

> > Jon

> > > Does anyone out there have a function that will round values in Excel
97
> to
> > > a specified number of significant figures?  Thanks.

Thu, 08 Jul 2004 16:09:51 GMT
significant figures
Did you qualify them with either application or worksheetfunction

Application.Round
Application.Log10

or

WorksheetFunction.round
WorksheetFunction.Log10

Regards,
Tom Ogilvy

Quote:
> Thanks, Jon.  This looks like what I need.  However, I'm having a problem
> when I try to use this formula in VBA code.  It chokes on both the Round
and
> Log10 functions and gives me a "Sub or function not defined" error even
> though these functions are supposed to be available to Visual Basic.  I'm
> unable to find a Knowledge Base article that addresses this.  Any ideas?
Do
> I need to set some obscure reference that I'm not aware of?  Thanks.

> Amy

> > =ROUND(A1,3-INT(LOG10(ABS(A1)))-1)

> > The 3 is the number of digits to round to.

> > Jon

> > > Does anyone out there have a function that will round values in Excel
97
> to
> > > a specified number of significant figures?  Thanks.

Fri, 09 Jul 2004 02:01:03 GMT
significant figures
Amy, here's a direct translation to VBA. Haralds solution works just as well.

=ROUND(A1,3-INT(LOG10(ABS(A1)))-1)

Function SigDigs(dblValue As Double, lngHowMany As Long) As Double
SigDigs = Application.Round(dblValue, lngHowMany -
Int(Application.Log10(Abs(dblValue))) - 1)
End Function

Jon

Quote:

> Thanks, Jon.  This looks like what I need.  However, I'm having a problem
> when I try to use this formula in VBA code.  It chokes on both the Round and
> Log10 functions and gives me a "Sub or function not defined" error even
> though these functions are supposed to be available to Visual Basic.  I'm
> unable to find a Knowledge Base article that addresses this.  Any ideas?  Do
> I need to set some obscure reference that I'm not aware of?  Thanks.

> Amy

> > =ROUND(A1,3-INT(LOG10(ABS(A1)))-1)

> > The 3 is the number of digits to round to.

> > Jon

> > > Does anyone out there have a function that will round values in Excel 97
> to
> > > a specified number of significant figures?  Thanks.

Fri, 09 Jul 2004 02:29:33 GMT
significant figures
Thanks to all who responded.  Guess I didn't word my original question
clearly.  Works like a charm once you qualify the functions.  You all are
going to make me look good at work tomorrow.  Thanks again.

Amy

Quote:
> Amy, here's a direct translation to VBA. Haralds solution works just as
well.

> =ROUND(A1,3-INT(LOG10(ABS(A1)))-1)

> Function SigDigs(dblValue As Double, lngHowMany As Long) As Double
>     SigDigs = Application.Round(dblValue, lngHowMany -
> Int(Application.Log10(Abs(dblValue))) - 1)
> End Function

> Jon

> > Thanks, Jon.  This looks like what I need.  However, I'm having a
problem
> > when I try to use this formula in VBA code.  It chokes on both the Round
and
> > Log10 functions and gives me a "Sub or function not defined" error even
> > though these functions are supposed to be available to Visual Basic.
I'm
> > unable to find a Knowledge Base article that addresses this.  Any ideas?
Do
> > I need to set some obscure reference that I'm not aware of?  Thanks.

> > Amy

> > > =ROUND(A1,3-INT(LOG10(ABS(A1)))-1)

> > > The 3 is the number of digits to round to.

> > > Jon

> > > > Does anyone out there have a function that will round values in
Excel 97
> > to
> > > > a specified number of significant figures?  Thanks.

Fri, 09 Jul 2004 09:43:46 GMT

 Page 1 of 1 [ 8 post ]

Relevant Pages