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

 

[kjv1611]

Thanks! Wow! Yeah, I'll definitely revisit later. They're still building more items onto my list of Excel fixes. Thanks for the help on this one.

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

 

[zelgar]

Here's a site that discusses how to do Banker Rounding in different ways:

https://www.extendoffice.com/documents/excel/1145-excel-bankers-rounding.html

I'm a little surprised that your new company is using banker's rounding since you indicate it's in a technical field.

 

[strongm]

Not sure on this occasion that that advances us any further.

Option 1 on that page is exactly the same as the vbRound function already provided here
Option 2 on that page just does the same thing, it just doesn't wrap the VBA Round function in a UDF (i.e. 1 and 2 are exactly the same thing)
Option 3 Requires a 3rd party add-in - which, given we can so easily do banker's rounding in Excel without an add-in, seems rather pointless

 

[kjv1611]

zelgar, I found one thing quite interesting about the "normal" rounding vs "banker's rounding" in the video I posted earlier. Here's the same link for easy reference:

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

Basically, "banker's rounding" ends up being more accurate over a series of variations vs normal rounding. Now, who determines what is more or less accurate is beyond me, but basically it shows less variation from the actual value when tested with a large sample size. This is demonstrated via multiple methods in the video.

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