Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
pls help newbie with my significant figures function
Hello. I am having a little trouble with a simple little function I wrote
to handle significant figures in excel. I just created a VBA module with this simeple code; Function Sig(BaseNum, NumSigDig) Sig = Round(BaseNum, NumSigDig - Len(Int(BaseNum))) End Function The intended functionality of this would be something like if R1C1= 42.0037 then =sig(a1,5) would return the number 42.004 (rounded to 5 significant digits). Now this function mostly seems to work like a charm (there are some limitations in this implementation but they are expected and not the subject of this post) What really has me stumped is the function seems to only work where BaseNum (the number to rounded) is less than 10000 (so 9999.9999999 works okay, but not 10000). I am very new to VBA so I don't understand how data types are being handled well enough to troubleshoot this problem. Any advice would be greatly appreciated. Thanks James |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
pls help newbie with my significant figures function
James Bond wrote in
: Hello. I am having a little trouble with a simple little function I wrote to handle significant figures in excel. I just created a VBA module with this simeple code; Function Sig(BaseNum, NumSigDig) Sig = Round(BaseNum, NumSigDig - Len(Int(BaseNum))) End Function The intended functionality of this would be something like if R1C1= 42.0037 then =sig(a1,5) would return the number 42.004 (rounded to 5 significant digits). Now this function mostly seems to work like a charm (there are some limitations in this implementation but they are expected and not the subject of this post) What really has me stumped is the function seems to only work where BaseNum (the number to rounded) is less than 10000 (so 9999.9999999 works okay, but not 10000). I am very new to VBA so I don't understand how data types are being handled well enough to troubleshoot this problem. Any advice would be greatly appreciated. Thanks James Oh, I probably should have mentioned that when I say my function doesn't work for values of 10000 or greater I mean that I get a #VALUE error in the the spreadsheet. Thanks again. James |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
pls help newbie with my significant figures function
On Sun, 29 Aug 2004 18:08:25 GMT, James Bond wrote:
Hello. I am having a little trouble with a simple little function I wrote to handle significant figures in excel. I just created a VBA module with this simeple code; Function Sig(BaseNum, NumSigDig) Sig = Round(BaseNum, NumSigDig - Len(Int(BaseNum))) End Function The intended functionality of this would be something like if R1C1= 42.0037 then =sig(a1,5) would return the number 42.004 (rounded to 5 significant digits). Now this function mostly seems to work like a charm (there are some limitations in this implementation but they are expected and not the subject of this post) What really has me stumped is the function seems to only work where BaseNum (the number to rounded) is less than 10000 (so 9999.9999999 works okay, but not 10000). I am very new to VBA so I don't understand how data types are being handled well enough to troubleshoot this problem. Any advice would be greatly appreciated. Thanks James Here are some routines to round to significant digits: VBA ============================== Function RoundSigDigits(N As Double, SigDigits As Integer) As Double RoundSigDigits = Application.WorksheetFunction.Round _ (N, Fix(-Log(Abs(N)) / Log(10)) + SigDigits + (Abs(N) 1)) End Function ======================== Various worksheet formulas: =ROUND(F1,TRUNC(-LOG(ABS(F1)))+SigDigits-(ABS(F1)1)) =ROUND(A1,SigDigits-1-INT(LOG10(ABS(A1)))) (Walkenbach) =--TEXT(A1,"."&REPT("0",SigDigits)&"E+000") (Harlan) ============================ --ron |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
pls help newbie with my significant figures function
On Sun, 29 Aug 2004 18:08:25 GMT, James Bond wrote:
Hello. I am having a little trouble with a simple little function I wrote to handle significant figures in excel. I just created a VBA module with this simeple code; Function Sig(BaseNum, NumSigDig) Sig = Round(BaseNum, NumSigDig - Len(Int(BaseNum))) End Function The intended functionality of this would be something like if R1C1= 42.0037 then =sig(a1,5) would return the number 42.004 (rounded to 5 significant digits). Now this function mostly seems to work like a charm (there are some limitations in this implementation but they are expected and not the subject of this post) What really has me stumped is the function seems to only work where BaseNum (the number to rounded) is less than 10000 (so 9999.9999999 works okay, but not 10000). I am very new to VBA so I don't understand how data types are being handled well enough to troubleshoot this problem. Any advice would be greatly appreciated. Thanks James I forgot to answer your question about why your routine didn't work. In recent versions of VBA, the VBA Round function is different from the Excel worksheet function. In the VBA Round function, I do not believe that numdecimalplaces can be negative. If you prefer your routine to the one's I provided, you could either restrict numdecimalplaces to being not less than zero, or use worksheetfunction.round --ron |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
pls help newbie with my significant figures function
Ron Rosenfeld wrote in
: I forgot to answer your question about why your routine didn't work. In recent versions of VBA, the VBA Round function is different from the Excel worksheet function. In the VBA Round function, I do not believe that numdecimalplaces can be negative. If you prefer your routine to the one's I provided, you could either restrict numdecimalplaces to being not less than zero, or use worksheetfunction.round --ron Ahhh, that would certainly explain the problems I was having. Thank you very much for that explanation as well as the info on other ways to achive my goals. James |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i show 3 significant figures | Excel Discussion (Misc queries) | |||
significant figures | Excel Worksheet Functions | |||
significant figures? | Excel Discussion (Misc queries) | |||
Rounding/Significant figures | Excel Worksheet Functions | |||
Significant figures (not decimal places) | Excel Worksheet Functions |