2

In Excel 2016 I need to show zeros out to the leading non-zero digits and then just the two digits after that. For example:

  • 0.00002423 should read 0.000024,
  • 0.242 should read 0.24,
  • 0.0032255 should read 0.0032,

and so forth.

3
  • 3
    You have chosen your examples badly -- they are all correctly rounded, simply because all of them round down. So we don't know whether you require correct rounding or not. What should 0.0032789 read as? 0.0032 or 0.0033? – TonyK May 12 at 14:54
  • 1
    Your third example is either a mistake or nonsense. – Kirk Woll May 12 at 21:38
  • By the way, these are known as "significant figures". So a phrasing of the question using the technical vocabulary would have been "how to round numbers to two significant figures in Excel?" — for which I am sure there are many google hits. – theonlygusti May 12 at 23:03
8

If rounding is required, you can also try this formula =ROUND(A1,INT(2-LOG(A1))).

enter image description here

4
  • In few cases it round the vale like 0.0000145 returns 0.000015 ,, should be 0.000014 !! – Rajesh S May 12 at 6:03
  • 3
    @RajeshS Well, there is always ROUNDDOWN() – Stian Yttervik May 12 at 11:50
  • 1
    you could combine both approaches: =LEFT(A1,LEN(ROUND(A1,INT(2-LOG(A1))))) – Yorik May 12 at 21:32
  • @Yorik,, this one is perfect, this =ROUND(A1,INT(2-LOG(A1))) doesn't works perfectly in few cases like for me 0.0000145 it returns 0.000015 !! – Rajesh S May 13 at 4:37
7

Try,

In B2 formula copied down :

=0+LEFT(A2,MIN(FIND({1,2,3,4,5,6,7,8,9},A2&1/17))+1)

enter image description here

Edit :

1] 1/17 =0.0588235294117647 which is called Pandigital number that contains digits from 0 to 9

2] A2&1/17 is a short form of A2&1234567890 in which to shorten the formula length.

3] Other Pandigital number can also be used :

A2&5^19

A2&7^18

A2&3^45

..........

4
  • It's good one +10 ☺ ,,, better explain use of 1/17,,, will help the community to solve many such issue!! – Rajesh S May 12 at 5:45
  • @RajeshS, Please see my post new editing. – bosco_yip May 12 at 6:37
  • @MátéJuhász, this formula is based on to find the the position of 1st digit, explanation please see : ablebits.com/office-addins-blog/2017/11/22/… – bosco_yip May 12 at 11:27
  • 3
    This isn't CodeGolf.SE. I'd just stick with A2&1234567890 for clarity. Anyone seeing this code years later is going to be very confused otherwise. – Darrel Hoffman May 12 at 20:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.