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:
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:
And the formula from Zathras in 2003 was:
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
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:
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:
Code:
=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:
Code:
=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