Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
significant figures
Here is my dilema: Have a huge spread sheet that takes hours of manual
calculations and turns it into a 5-10min process. However i am in the process of validating it but in my eyes its not good enough. In my spreadsheet i need to keep 4 significant figures in most of my showing calc's but the input values change. So for instance, I get .004454, 0.00004436, and 0.0004853. I need to keep 4 significant figures at all times. But i cannot use the round function the way that excel help has it definded. i can only get it to keep "X" amount of digets which is not what i want. Is there anything i can do to change this or is it asking to much of excel? Thanks, Crystal |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
significant figures
need to keep 4 significant figures at all times.
0.004454, 0.00004436, and 0.0004853. What should those values be with 4 significant figures? -- Biff Microsoft Excel MVP "Crystal" wrote in message ... Here is my dilema: Have a huge spread sheet that takes hours of manual calculations and turns it into a 5-10min process. However i am in the process of validating it but in my eyes its not good enough. In my spreadsheet i need to keep 4 significant figures in most of my showing calc's but the input values change. So for instance, I get .004454, 0.00004436, and 0.0004853. I need to keep 4 significant figures at all times. But i cannot use the round function the way that excel help has it definded. i can only get it to keep "X" amount of digets which is not what i want. Is there anything i can do to change this or is it asking to much of excel? Thanks, Crystal |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
significant figures
"Crystal" wrote:
i need to keep 4 significant figures in most of my showing calc's but the input values change. So for instance, I get .004454, 0.00004436, and 0.0004853. If the original is in B1, try: =--(LEFT(TEXT(B1,"0.000E+0"),5) & MID(TEXT(B1,"0.000E+0"),6,5)) Note: This rounds the 5th significant digit. Is that okay? If not, then try: =--(LEFT(TEXT(B1,"0.0000E+0"),5) & MID(TEXT(B1,"0.0000E+0"),7,5)) ----- original message ---- "Crystal" wrote in message ... Here is my dilema: Have a huge spread sheet that takes hours of manual calculations and turns it into a 5-10min process. However i am in the process of validating it but in my eyes its not good enough. In my spreadsheet i need to keep 4 significant figures in most of my showing calc's but the input values change. So for instance, I get .004454, 0.00004436, and 0.0004853. I need to keep 4 significant figures at all times. But i cannot use the round function the way that excel help has it definded. i can only get it to keep "X" amount of digets which is not what i want. Is there anything i can do to change this or is it asking to much of excel? Thanks, Crystal |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
significant figures
Perhaps you could use this UDF (user defined function) to do that...
Function RoundSignificantFigures(Value As Variant, _ Significance As Long) As Double Dim Num As String Dim Parts() As String Num = Format(Value, "0.##############################e+0;;0") Parts = Split(CStr(Num), "E", , vbTextCompare) If CDbl(Parts(0)) = 0 Then RoundSignificantFigures = 0 Else RoundSignificantFigures = CDbl(Format(Parts(0), "0" & _ Left(".", -(Significance < 0)) & _ String(Significance - 1, "0")) & _ "E" & Parts(1)) End If End Function Just put your calculations inside a call to this function and specify 4 for the last argument. For example, if your cell has this simple SUM function call =SUM(A1:A100) then you could change it to this... =RoundSignificantFigures(SUM(A1:A100),4) -- Rick (MVP - Excel) "Crystal" wrote in message ... Here is my dilema: Have a huge spread sheet that takes hours of manual calculations and turns it into a 5-10min process. However i am in the process of validating it but in my eyes its not good enough. In my spreadsheet i need to keep 4 significant figures in most of my showing calc's but the input values change. So for instance, I get .004454, 0.00004436, and 0.0004853. I need to keep 4 significant figures at all times. But i cannot use the round function the way that excel help has it definded. i can only get it to keep "X" amount of digets which is not what i want. Is there anything i can do to change this or is it asking to much of excel? Thanks, Crystal |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
significant figures
In case you are new to UDF's, they are installed as follows... click Alt+F11
to go into the VB editor, click Insert/Module from its menu bar and then copy/paste my code into the code window that opened up. That's it... you can now use RoundSignificantFigures just like a built-in worksheet function. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Perhaps you could use this UDF (user defined function) to do that... Function RoundSignificantFigures(Value As Variant, _ Significance As Long) As Double Dim Num As String Dim Parts() As String Num = Format(Value, "0.##############################e+0;;0") Parts = Split(CStr(Num), "E", , vbTextCompare) If CDbl(Parts(0)) = 0 Then RoundSignificantFigures = 0 Else RoundSignificantFigures = CDbl(Format(Parts(0), "0" & _ Left(".", -(Significance < 0)) & _ String(Significance - 1, "0")) & _ "E" & Parts(1)) End If End Function Just put your calculations inside a call to this function and specify 4 for the last argument. For example, if your cell has this simple SUM function call =SUM(A1:A100) then you could change it to this... =RoundSignificantFigures(SUM(A1:A100),4) -- Rick (MVP - Excel) "Crystal" wrote in message ... Here is my dilema: Have a huge spread sheet that takes hours of manual calculations and turns it into a 5-10min process. However i am in the process of validating it but in my eyes its not good enough. In my spreadsheet i need to keep 4 significant figures in most of my showing calc's but the input values change. So for instance, I get .004454, 0.00004436, and 0.0004853. I need to keep 4 significant figures at all times. But i cannot use the round function the way that excel help has it definded. i can only get it to keep "X" amount of digets which is not what i want. Is there anything i can do to change this or is it asking to much of excel? Thanks, Crystal |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
significant figures
Errata...
I wrote: =--(LEFT(TEXT(B1,"0.000E+0"),5) & MID(TEXT(B1,"0.000E+0"),6,5)) [....] =--(LEFT(TEXT(B1,"0.0000E+0"),5) & MID(TEXT(B1,"0.0000E+0"),7,5)) Those formulas do not handle negative numbers correctly Instead, try the following (rounding the 5th significant digit): =--(LEFT(TEXT(B1,"+0.000E+0;-0.000E+0"),6) & MID(TEXT(B1,"+0.000E+0;-0.000E+0"),7,5)) Or the following (truncating after the 4th significant digit): =--(LEFT(TEXT(B1,"+0.0000E+0;-0.0000E+0"),6) & MID(TEXT(B1,"+0.0000E+0;-0.0000E+0"),8,5)) ----- original message ----- "JoeU2004" wrote in message ... "Crystal" wrote: i need to keep 4 significant figures in most of my showing calc's but the input values change. So for instance, I get .004454, 0.00004436, and 0.0004853. If the original is in B1, try: =--(LEFT(TEXT(B1,"0.000E+0"),5) & MID(TEXT(B1,"0.000E+0"),6,5)) Note: This rounds the 5th significant digit. Is that okay? If not, then try: =--(LEFT(TEXT(B1,"0.0000E+0"),5) & MID(TEXT(B1,"0.0000E+0"),7,5)) ----- original message ---- "Crystal" wrote in message ... Here is my dilema: Have a huge spread sheet that takes hours of manual calculations and turns it into a 5-10min process. However i am in the process of validating it but in my eyes its not good enough. In my spreadsheet i need to keep 4 significant figures in most of my showing calc's but the input values change. So for instance, I get .004454, 0.00004436, and 0.0004853. I need to keep 4 significant figures at all times. But i cannot use the round function the way that excel help has it definded. i can only get it to keep "X" amount of digets which is not what i want. Is there anything i can do to change this or is it asking to much of excel? Thanks, Crystal |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
significant figures
"JoeU2004" wrote...
.... Instead, try the following (rounding the 5th significant digit): =--(LEFT(TEXT(B1,"+0.000E+0;-0.000E+0"),6) & * * * MID(TEXT(B1,"+0.000E+0;-0.000E+0"),7,5)) Why not the far simpler =--TEXT(B1,".0000E+000") ? Or =ROUND(B1,INT(4-LOG10(ABS(B1)))) Or the following (truncating after the 4th significant digit): =--(LEFT(TEXT(B1,"+0.0000E+0;-0.0000E+0"),6) & * * * MID(TEXT(B1,"+0.0000E+0;-0.0000E+0"),8,5)) =TRUNC(B1,INT(4-LOG10(ABS(B1)))) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
significant figures
"Harlan Grove" wrote:
"JoeU2004" wrote... Instead, try the following (rounding the 5th significant digit): =--(LEFT(TEXT(B1,"+0.000E+0;-0.000E+0"),6) & MID(TEXT(B1,"+0.000E+0;-0.000E+0"),7,5)) Why not the far simpler =--TEXT(B1,".0000E+000") Yes, m-u-c-h better for the rounding case. Deja vu! :) I think "E+0" would suffice. And FYI, I write 0.000E+0 out of habit. I don't think it makes a significant difference -- no pun intended. ;) Or the following (truncating after the 4th significant digit): =--(LEFT(TEXT(B1,"+0.0000E+0;-0.0000E+0"),6) & MID(TEXT(B1,"+0.0000E+0;-0.0000E+0"),8,5)) =TRUNC(B1,INT(4-LOG10(ABS(B1)))) Well, perhaps: =IF(B1=0,0,TRUNC(B1,INT(4-LOG10(ABS(B1))))) I'm just a tad squemish about using the LOG function; I worry about numerical corner cases in the binary world. But I'm probably wrong. I agree: mathematically, it should work fine, especially since the largest decimal exponent is relatively small (+/-308). PS: My truncation formulation is incorrect. Try 1.23999999999999. If there is any problem with the TRUNC expression, I would go with: =SIGN(B1)*(LEFT(TEXT(ABS(B1),"0.00000000000000E+0" ),5) & MID(TEXT(ABS(B1),"0.00000000000000E+0"),17,5)) |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
significant figures
Yet another errata, just for posterity....
I wrote: Or the following (truncating after the 4th significant digit): =--(LEFT(TEXT(B1,"+0.0000E+0;-0.0000E+0"),6) & MID(TEXT(B1,"+0.0000E+0;-0.0000E+0"),8,5)) That is wrong; try 1.23999999999999. At this point, I would opt for the briefer form: =SIGN(B1)*(LEFT(TEXT(ABS(B1),"0.00000000000000E+0" ),5) & MID(TEXT(ABS(B1),"0.00000000000000E+0"),17,5)) (But see Harlan's better formulas.) ----- original message ----- "JoeU2004" wrote in message ... Errata... I wrote: =--(LEFT(TEXT(B1,"0.000E+0"),5) & MID(TEXT(B1,"0.000E+0"),6,5)) [....] =--(LEFT(TEXT(B1,"0.0000E+0"),5) & MID(TEXT(B1,"0.0000E+0"),7,5)) Those formulas do not handle negative numbers correctly Instead, try the following (rounding the 5th significant digit): =--(LEFT(TEXT(B1,"+0.000E+0;-0.000E+0"),6) & MID(TEXT(B1,"+0.000E+0;-0.000E+0"),7,5)) Or the following (truncating after the 4th significant digit): =--(LEFT(TEXT(B1,"+0.0000E+0;-0.0000E+0"),6) & MID(TEXT(B1,"+0.0000E+0;-0.0000E+0"),8,5)) ----- original message ----- "JoeU2004" wrote in message ... "Crystal" wrote: i need to keep 4 significant figures in most of my showing calc's but the input values change. So for instance, I get .004454, 0.00004436, and 0.0004853. If the original is in B1, try: =--(LEFT(TEXT(B1,"0.000E+0"),5) & MID(TEXT(B1,"0.000E+0"),6,5)) Note: This rounds the 5th significant digit. Is that okay? If not, then try: =--(LEFT(TEXT(B1,"0.0000E+0"),5) & MID(TEXT(B1,"0.0000E+0"),7,5)) ----- original message ---- "Crystal" wrote in message ... Here is my dilema: Have a huge spread sheet that takes hours of manual calculations and turns it into a 5-10min process. However i am in the process of validating it but in my eyes its not good enough. In my spreadsheet i need to keep 4 significant figures in most of my showing calc's but the input values change. So for instance, I get .004454, 0.00004436, and 0.0004853. I need to keep 4 significant figures at all times. But i cannot use the round function the way that excel help has it definded. i can only get it to keep "X" amount of digets which is not what i want. Is there anything i can do to change this or is it asking to much of excel? Thanks, Crystal |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
significant figures
"Rick Rothstein" wrote:
Num = Format(Value, "0.##############################e+0;;0") I'm curious: why do you have more fractional digits than can be formatted? For VB 6 in Excel 2003, Format does not seem to format beyond 15 significant digits. Does VB in Excel 2007 format at least 31? Anyway, the following function handles both rounding and truncating to a variable number of significant digits. Change maxsig to 31, if appropriate. Function vround(val As Double, sig As Integer, Optional trnc As Boolean = False) As Double Const maxsig As Integer = 15 Dim s As String, dig As Integer If sig <= 0 Then sig = 1 Else If sig maxsig Then sig = maxsig dig = IIf(trnc, maxsig, sig) s = Format(Abs(val), "." & String(dig, "0") & "E+0") vround = Sgn(val) * (Left(s, sig + 1) & Mid(s, dig + 2, 5)) End Function Usage: Round: =vround(A1,4) Truncate: =vround(A1,4,1) ----- original message ----- "Rick Rothstein" wrote in message ... Perhaps you could use this UDF (user defined function) to do that... Function RoundSignificantFigures(Value As Variant, _ Significance As Long) As Double Dim Num As String Dim Parts() As String Num = Format(Value, "0.##############################e+0;;0") Parts = Split(CStr(Num), "E", , vbTextCompare) If CDbl(Parts(0)) = 0 Then RoundSignificantFigures = 0 Else RoundSignificantFigures = CDbl(Format(Parts(0), "0" & _ Left(".", -(Significance < 0)) & _ String(Significance - 1, "0")) & _ "E" & Parts(1)) End If End Function Just put your calculations inside a call to this function and specify 4 for the last argument. For example, if your cell has this simple SUM function call =SUM(A1:A100) then you could change it to this... =RoundSignificantFigures(SUM(A1:A100),4) -- Rick (MVP - Excel) "Crystal" wrote in message ... Here is my dilema: Have a huge spread sheet that takes hours of manual calculations and turns it into a 5-10min process. However i am in the process of validating it but in my eyes its not good enough. In my spreadsheet i need to keep 4 significant figures in most of my showing calc's but the input values change. So for instance, I get .004454, 0.00004436, and 0.0004853. I need to keep 4 significant figures at all times. But i cannot use the round function the way that excel help has it definded. i can only get it to keep "X" amount of digets which is not what i want. Is there anything i can do to change this or is it asking to much of excel? Thanks, Crystal |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
significant figures
On Tue, 7 Jul 2009 19:07:01 -0700, Crystal
wrote: Here is my dilema: Have a huge spread sheet that takes hours of manual calculations and turns it into a 5-10min process. However i am in the process of validating it but in my eyes its not good enough. In my spreadsheet i need to keep 4 significant figures in most of my showing calc's but the input values change. So for instance, I get .004454, 0.00004436, and 0.0004853. I need to keep 4 significant figures at all times. But i cannot use the round function the way that excel help has it definded. i can only get it to keep "X" amount of digets which is not what i want. Is there anything i can do to change this or is it asking to much of excel? Thanks, Crystal One possible issue with some of the other solutions offered is that trailing zero's are dropped. So, for example, 1.2 is expressed as 1.2 and not as 1.200. One possible solution, which may or may not be acceptable on your worksheet, would be to use scientific notation -- just custom format your numbers as 0.000E+00 You would then see: 0.004544 -- 4.544E-03 0.00004436 -- 4.436E-05 0.0004853 -- 4.853E-04 1.2 -- 1.200E+00 --ron |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
significant figures
Alternatvely, following Harlan's leading, and breaking the coincidental
cohesion (shame on me!).... Function vround(val As Double, sig As Integer) As Double Const maxsig As Integer = 15 If sig <= 0 Then sig = 1 Else If sig maxsig Then sig = maxsig vround = --Format(val, "." & String(sig, "0") & "E+0") End Function or the Excel formula: =--text(A1,"."&rept("0",B1)&"E+0") Function vtrunc(val As Double, sig As Integer) As Double Const maxsig As Integer = 15 Dim s As String If sig <= 0 Then sig = 1 Else If sig maxsig Then sig = maxsig ' maxsig zeros; alternatively: "."&String(maxsig,"0")&"E+0" s = Format(Abs(val), ".000000000000000E+0") vtrunc = Sgn(val) * (Left(s, sig + 1) & Mid(s, maxsig + 2, 5)) End Function or the Excel formula: =sign(A1)*(left(text(abs(A1),".000000000000000E+0" ),B1+1) & mid(text(abs(A1),".000000000000000E+0"),17,5)) where A1 is the value and B1 is the number of significant digits. ----- original message ----- "JoeU2004" wrote in message ... "Rick Rothstein" wrote: Num = Format(Value, "0.##############################e+0;;0") I'm curious: why do you have more fractional digits than can be formatted? For VB 6 in Excel 2003, Format does not seem to format beyond 15 significant digits. Does VB in Excel 2007 format at least 31? Anyway, the following function handles both rounding and truncating to a variable number of significant digits. Change maxsig to 31, if appropriate. Function vround(val As Double, sig As Integer, Optional trnc As Boolean = False) As Double Const maxsig As Integer = 15 Dim s As String, dig As Integer If sig <= 0 Then sig = 1 Else If sig maxsig Then sig = maxsig dig = IIf(trnc, maxsig, sig) s = Format(Abs(val), "." & String(dig, "0") & "E+0") vround = Sgn(val) * (Left(s, sig + 1) & Mid(s, dig + 2, 5)) End Function Usage: Round: =vround(A1,4) Truncate: =vround(A1,4,1) ----- original message ----- "Rick Rothstein" wrote in message ... Perhaps you could use this UDF (user defined function) to do that... Function RoundSignificantFigures(Value As Variant, _ Significance As Long) As Double Dim Num As String Dim Parts() As String Num = Format(Value, "0.##############################e+0;;0") Parts = Split(CStr(Num), "E", , vbTextCompare) If CDbl(Parts(0)) = 0 Then RoundSignificantFigures = 0 Else RoundSignificantFigures = CDbl(Format(Parts(0), "0" & _ Left(".", -(Significance < 0)) & _ String(Significance - 1, "0")) & _ "E" & Parts(1)) End If End Function Just put your calculations inside a call to this function and specify 4 for the last argument. For example, if your cell has this simple SUM function call =SUM(A1:A100) then you could change it to this... =RoundSignificantFigures(SUM(A1:A100),4) -- Rick (MVP - Excel) "Crystal" wrote in message ... Here is my dilema: Have a huge spread sheet that takes hours of manual calculations and turns it into a 5-10min process. However i am in the process of validating it but in my eyes its not good enough. In my spreadsheet i need to keep 4 significant figures in most of my showing calc's but the input values change. So for instance, I get .004454, 0.00004436, and 0.0004853. I need to keep 4 significant figures at all times. But i cannot use the round function the way that excel help has it definded. i can only get it to keep "X" amount of digets which is not what i want. Is there anything i can do to change this or is it asking to much of excel? Thanks, Crystal |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
significant figures
"Ron Rosenfeld" wrote:
One possible issue with some of the other solutions offered is that trailing zero's are dropped. Excellent point! We need clarification from Crystal on the requirements. I interpreted "keep 4 significant figures" to mean change the value. You are interpreting it to mean simply change the display. To that end, Crystal could want the Number format with a variable number of significant digits. And Crystal might want both: change the value and the display. The latter (variable Number format) is doable, probably more easily in a UDF, but perhaps feasible in an Excel formula. But I would like to see Crystal's clarification before going off on further tangents. ----- original message ----- "Ron Rosenfeld" wrote in message ... On Tue, 7 Jul 2009 19:07:01 -0700, Crystal wrote: Here is my dilema: Have a huge spread sheet that takes hours of manual calculations and turns it into a 5-10min process. However i am in the process of validating it but in my eyes its not good enough. In my spreadsheet i need to keep 4 significant figures in most of my showing calc's but the input values change. So for instance, I get .004454, 0.00004436, and 0.0004853. I need to keep 4 significant figures at all times. But i cannot use the round function the way that excel help has it definded. i can only get it to keep "X" amount of digets which is not what i want. Is there anything i can do to change this or is it asking to much of excel? Thanks, Crystal One possible issue with some of the other solutions offered is that trailing zero's are dropped. So, for example, 1.2 is expressed as 1.2 and not as 1.200. One possible solution, which may or may not be acceptable on your worksheet, would be to use scientific notation -- just custom format your numbers as 0.000E+00 You would then see: 0.004544 -- 4.544E-03 0.00004436 -- 4.436E-05 0.0004853 -- 4.853E-04 1.2 -- 1.200E+00 --ron |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
significant figures
See inline comments...
Num = Format(Value, "0.##############################e+0;;0") I'm curious: why do you have more fractional digits than can be formatted? For VB 6 in Excel 2003, Format does not seem to format beyond 15 significant digits. Does VB in Excel 2007 format at least 31? The function I posted was a modified version of an old function I had written for the compiled VB world. That function originally returned a Variant (I changed the return type to Double because the OP wanted the function for the spreadsheet, which also necessitated the CDbl check for 0). The reason I returned a variant is because the Format function is not limited to 14 significant digits as you have said... it can round values (passed in as String values) up to 29 digits without a decimal point and 28 digits with a decimal point (it appears to be using a Decimal sub-type of a Variant for its first argument). You can see this with this example (run it in the Immediate window)... ? Format("123.456789098765432101234567898765432", "0." & String(25,"#")) This will return an answer of 123.4567890987654321012345679. Of course, to hold the accuracy, this value must be preserved as a String (like when you would assign it to a TextBox or concatenate it with other text). While I am having trouble relocating my original function, here is the function I posted modified to handle more than 15 significant digits. Function RoundSignificantFigures(Value As Variant, _ Significance As Long) As Variant Dim Num As String Dim Parts() As String Num = Format(Value, "0.##############################e+0;;0") Parts = Split(CStr(Num), "E", , vbTextCompare) If Parts(0) = 0 And UBound(Parts) = 0 Then Else RoundSignificantFigures = Format(Parts(0), "0" & _ Left(".", -(Significance < 0)) & _ String(Significance - 1, "#")) & _ "E" & Format(Parts(1), "#00") End If End Function You can use this inside of VBA code as necessary or on a worksheet if the cell providing the first argument is formatted as Text... Function RoundSignificantFigures(Value As Variant, _ Significance As Long) As Variant Dim Num As String Dim Parts() As String Num = Format(Value, "0.##############################e+0;;0") Parts = Split(CStr(Num), "E", , vbTextCompare) If Parts(0) = 0 And UBound(Parts) = 0 Then Else RoundSignificantFigures = Format(Parts(0), "0" & _ Left(".", -(Significance < 0)) & _ String(Significance - 1, "#")) & _ "E" & Format(Parts(1), "#00") End If End Function Anyway, the following function handles both rounding and truncating to a variable number of significant digits. Change maxsig to 31, if appropriate. I just wanted to mentiont than the function I posted works fine even with the extra # signs. -- Rick (MVP - Excel) Function vround(val As Double, sig As Integer, Optional trnc As Boolean = False) As Double Const maxsig As Integer = 15 Dim s As String, dig As Integer If sig <= 0 Then sig = 1 Else If sig maxsig Then sig = maxsig dig = IIf(trnc, maxsig, sig) s = Format(Abs(val), "." & String(dig, "0") & "E+0") vround = Sgn(val) * (Left(s, sig + 1) & Mid(s, dig + 2, 5)) End Function Usage: Round: =vround(A1,4) Truncate: =vround(A1,4,1) ----- original message ----- "Rick Rothstein" wrote in message ... Perhaps you could use this UDF (user defined function) to do that... Function RoundSignificantFigures(Value As Variant, _ Significance As Long) As Double Dim Num As String Dim Parts() As String Num = Format(Value, "0.##############################e+0;;0") Parts = Split(CStr(Num), "E", , vbTextCompare) If CDbl(Parts(0)) = 0 Then RoundSignificantFigures = 0 Else RoundSignificantFigures = CDbl(Format(Parts(0), "0" & _ Left(".", -(Significance < 0)) & _ String(Significance - 1, "0")) & _ "E" & Parts(1)) End If End Function Just put your calculations inside a call to this function and specify 4 for the last argument. For example, if your cell has this simple SUM function call =SUM(A1:A100) then you could change it to this... =RoundSignificantFigures(SUM(A1:A100),4) -- Rick (MVP - Excel) "Crystal" wrote in message ... Here is my dilema: Have a huge spread sheet that takes hours of manual calculations and turns it into a 5-10min process. However i am in the process of validating it but in my eyes its not good enough. In my spreadsheet i need to keep 4 significant figures in most of my showing calc's but the input values change. So for instance, I get .004454, 0.00004436, and 0.0004853. I need to keep 4 significant figures at all times. But i cannot use the round function the way that excel help has it definded. i can only get it to keep "X" amount of digets which is not what i want. Is there anything i can do to change this or is it asking to much of excel? Thanks, Crystal |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
significant figures
On Wed, 8 Jul 2009 10:02:13 -0700, "JoeU2004" wrote:
Excellent point! We need clarification from Crystal on the requirements. I interpreted "keep 4 significant figures" to mean change the value. You are interpreting it to mean simply change the display. To that end, Crystal could want the Number format with a variable number of significant digits. And Crystal might want both: change the value and the display. The latter (variable Number format) is doable, probably more easily in a UDF, but perhaps feasible in an Excel formula. But I would like to see Crystal's clarification before going off on further tangents. Hopefully, she will post back shortly. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Significant figures as a conditional format | Excel Worksheet Functions | |||
significant figures? | Excel Discussion (Misc queries) | |||
Automatic formatting of Significant Figures .... | Excel Discussion (Misc queries) | |||
Rounding/Significant figures | Excel Worksheet Functions | |||
Significant figures (not decimal places) | Excel Worksheet Functions |