Formula to Show 3 Significant Figures/Digits from calculated results regardless of trailing zero 5

[kjv1611]

I'm now with a new company. Was laid off from last company after 16 years, and now I'm learning ropes at a new company (new to me) that's in a totally different industry. It's pretty exciting, actually, so don't go thinking I'm down if you read that first part.

This company uses a lot of scientific testing and methods, and in Excel, they were able to move towards using formulas and VBA to simplify some of their work they previously carried out by hand. The only problem is that over the past 3 to 4 years, it seems the folks they had working on these caused more harm than good. So now I've got plenty to troubleshoot and fix and cleanup.

Now for the issue at hand.

One issue I found was in some VBA code that was built (or mostly copied/pasted off Internet) just does NOT work. I found the same or similar code and directly tested it to be sure, and went through it, and it may work in some limited scenarios, but mostly it does not work.

So then I thought I wonder if there is a way to do it via a formula. Rather than reinvent the wheel, I searched online. The method I liked the most so far I found here:

http://www.pcreview.co.uk/threads/format-cells-number-to-3-significant-figures.3390066/

I have to say, I think that's a pretty cool way of handling it. And it seemed it was going to fit the bill, however I found 2 calculated results so far after additional testing that do not work. I'd say they almost work, but just don't quite make the finish line.

So I did some more searching, and searched here on Tek-Tips specifically. Then I found this formula which seems to work except it fails on the exact same values as the above solution. Here's the tek-tips thread:
thread68-555683

And now for some sample values and what I expect to get from them:
[pre]
Original 3 Significant Digits/Figures
31.81208054 31.8
31.25 31.2
[highlight #FCE94F]31.98438055 32.0[/highlight]
75.43245589 75.4
0.11141 0.11
1.1111 1.11
11.111111 11.1
[/pre]
The highlighted row is the problem. I'm getting 32 rather than 32.0.

The formula I have in place is this:

=LEFT(TEXT([b]OrigValue[/b],"0.00E+00"),4)*10^RIGHT(TEXT([b]OrigValue[/b],"0.00E+00"),3)

And the formula from Zathras in 2003 was:

=ROUND([b]OrigValue[/b],2-INT(LOG([b]OrigValue[/b])))

And lastly, I'm formatting the cells as General format. If I try to use numeric, then it'll go to whatever decimals I provide rather than what works for that one value.


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

[pre]
=LEFT(LEFT(TEXT(OrigValue,"0.00E+00"),4)*10^RIGHT(TEXT(OrigValue,"0.00E+00"),3)&".000",4)
[/pre]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kjv1611]

Skip,

That's awesome! Thanks. When I grow up, I hope I'll be as slick as you on formulas and such in Excel.

One small issue that may not matter - I have to find out. Is in the case when the final value is 100, I don't think they want to see "100." which ends up being the case with this modification. But if they are fine with that, then it'll be a rock solid solution. I can imagine a scenario where I simply use another IF function to say if "value=100", then just use 100, otherwise do the other stuff.

Thanks again!

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[strongm]

Or just apply

=ROUND(OriginalValue,1)

and apply a custom format:

[<100]#,##0.0;[>99]#,##0

 

[kjv1611]

strongm,

That's awesome! I found examples of custom formats online, and tried to tinker a bit, but everything failed so far. This one passes everything except for zero values.

Any way to force the 0.00 presentation when the value is zero?

There is one more thing to keep in mind with the rounding. I don't understand the what and why of it, but their method of rounding seems odd to me. I don't think the ROUND function will handle it the way they want.

I was told they "round to the even if 50 or .50". I verbally discussed this one, but I'm going to present some further examples via email to make 100% sure I'm clear on that one. I thought I had it straight, but I think it still baffles me. I'll post back with the final answer once I get that. It may be tomorrow. So the end of that one means that to go this route, I may end up needing to use a different function. But it seems to me that whatever method required, the custom number format should take care of the rest.

If there is no way to handle the zero value, and the rounding can work the way I'm told it's supposed to work, then it seems like strongm's method will work. I just spoke with the manager who sent me down the rabbit trail, and she advised it's really rare to ever see a zero value there, and even if there is one, they are not concerned in that scenario. So this one seems like the real winner at this point.

Any of the solutions would be better than what they had, but I like strongm's being so short. I earlier tried playing with ROUND, but just didn't go far enough, I guess.

I'll continue testing to be sure whichever one I end up using still gives the desired results.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[strongm]

[>=100]#,##0;[>0]#,##0.0;0.00

 

[strongm]

>round to the even if 50 or .50

That's just something more commonly called banker's (or financial) rounding. Whilst the worksheet ROUND function does arithmetic rounding, most of VBA's various rounding functions do banker's rounding. So you just need a very simple little custom function as a drop-in replacement for Exel's ROUND:

[blue]Public Function vbRound(Number As Double, Optional Num_Digits = 0) As Double
    vbRound = Round(Number, Num_Digits)
End Function[/blue]

 

[kjv1611]

strongm, Thanks! Embarrassed that I didn't realize that Round in VBA worked differently than the Excel function for ROUND. My oh my. And thanks for giving me a common name for the rounding. Gives me something to search on for better understanding going forward. I'll try the modified custom format as well.

Found a good video pulling everything together from the ExcelIsFun Youtube channel:

https://www.youtube.com/watch?v=Li5DurykVVM

He gives a few examples of how to get the result, and also gives a bit of background. It helped me, I know.

So yeah, strongm, that format works excellent, and I now know that the custom function will work. I'm not certain yet which way I want to go. I'm glad someone else gave you a star, b/c I'd of liked to offer a few myself.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

You know, this method:
=ROUND(OriginalValue,1)

nor this method:
vbRound(Number As Double, Optional Num_Digits = 0)

will work in my scenario. I forgot until I was about to put one of them in place.

This one also will not work: (found here)
=ROUND(A2,3)-(MOD(A2*10000,20)=5)/1000

The reason, from my examples, is I never know whether it's 0 decimal places, 1, 2, 3, etc. I have to go according to the values.

So the original formula I posted handled most scenarios, but not all. Skip's modification works except for with leaving the trailing decimal if the value is 100.

Does anyone have any ideas on getting rid of the trailing decimal in Skip's modified formula? I've got a wild idea I'll try, but it'll make the formula that much longer. I can embed the other with an IF clause, and say if the value ends up being 100, then do a left(value,3), and I Think that'll take care of it. I'll give that a shot, but if anyone else has a better idea, I'm all ears/eyes.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

My add-on to the other formula doesn't quite work.

The odd thing is, and I'm trying to get some clarification here, that in one location, I don't seem to run into issues with my original formula, but in the second location I do. And in that second location, in all examples I'm given, they only have either 0 or 1 decimal places (100 = zero decimal places). So it may be that strongm's suggestion will work there, but not in the first instance. I'll track that question down here to be certain.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

OK, I did some additional testing. Here's what I did:

I took Skips modification, and embedded it in an IF clause.

So the final formula looks something like this (within another formula):

=IF(LEFT(LEFT(TEXT(OrigValue,"0.00E+00"),4)*10^RIGHT(TEXT(OrigValue,"0.00E+00"),3)&".000",4)="100.",LEFT(LEFT(TEXT(OrigValue,"0.00E+00"),4)*10^RIGHT(TEXT(OrigValue,"0.00E+00"),3)&".000",3),LEFT(LEFT(TEXT(OrigValue,"0.00E+00"),4)*10^RIGHT(TEXT(OrigValue,"0.00E+00"),3)&".000",4))

And that seems to work for all the scenarios, and I leave out VBA and I leave the number format to General.

It's one long crazy formula, but it seems to work for all the given scenarios.

Thanks again to both SkipVought and StrongM!

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[zelgar]

Your final solution is not correct for the value 0.1141 as it gives the result of 0.11 (only 2 significant digits). The 0 before the decimal is not considered a significant digit. P

 

[kjv1611]

Blimey. I'll see what the lady here says. Thanks for pointing out the test.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Although I would LOVE LOVE LOVE to find a solution where it will always work, I was on the other hand thrilled when I just found out that the type example given of 0.1141 would never occur in the given scope of work, and therefore they are not concerned about solving for it.

If anyone wants to take a whack at solving for all possible digits, be my guest! It'd be an interesting puzzle, at least, and someone might need to cover wider scenarios.

For me, I have to skip along to several other things. They want to keep feeding me different Excel issues they've been dealing with, so I dare not dally where I don't have to.

Thanks again for anyone who offered suggestions.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[strongm]

>I never know whether it's 0 decimal places, 1, 2, 3

So, presumably, you have a rule ... (I'm just trying to understand what you actualy mean))

 

[strongm]

Public Function Example(myRange As Range, Optional Sig_Digits = 0) As String
Dim Num_Digits As Long
Num_Digits = Sig_Digits - (1 + Int(Logn(Abs(myRange))))
Example = Format(Round(myRange, Num_Digits), "0" & IIf(Num_Digits > 0, "." & String(Num_Digits, "0"), ""))
End Function

Public Function Logn(mynum As Double, Optional n As Long = 10) As Double
If mynum <> 0 Then Logn = Log(mynum) / Log
End Function

 

[kjv1611]

Honestly, I was given very little information regarding the how or why. I was shown the workbooks, said, "this is broken, we want it fixed", and that was that. I have been told some things, but I didn't know the last example value was not one that would ever occur in the given scenario until I asked the question. Apparently all possible results for a given step in the given test would end in something like 1.95 or some similar value. I've seen little variance.

But I'll try to revisit the Significant digits function you proposed there later. Might be a winner, but I have to move on to other things for now. Thanks again.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[zelgar]

Probably the easiest and best way would be a combination of using the =Round(OrigValue, 2- Int(Log(OrigValue) formula and using a conditional formatting using a formula to determine which cells to format with a formula = Len(OrigValue)=2 and the Format using the number format with 1 decimal place.

 

[strongm]

I thought we'd already determined that the worksheet Round function didn't meet the requirements, since it doesn't do banker's rounding?

 

[strongm]

Here's a slightly improved version of the function I posted:

[blue]Public Function Example(myRange As Range, Optional Sig_Digits = 0) As String
    Dim Num_Digits As Long
    Num_Digits = Sig_Digits - (1 + Int(Logn(Abs(myRange))))
    Example = Format(Round(myRange * 10 ^ Num_Digits) / 10 ^ Num_Digits, "0" & IIf(Num_Digits > 0, "." & String(Abs(Num_Digits), "0"), ""))
End Function

 Public Function Logn(mynum As Double, Optional n As Long = 10) As Double
    If mynum <> 0 Then Logn = Log(mynum) / Log(n)
 End Function
[/blue]