How to display fractions as decimal sixteenths

Question

Ok so this a srange request. I want excel to display fractions and decimals in decimal sixteenths. What I mean by that is 1/16 is .01 1/8 is .02 3/16 is .03 so on and so forth. Is there any way to accomplish this?

Answer 1

How is your data represented?

If you have text consisting of some digit(s) followed by a "/" followed by "16":

• The FIND function will tell you where the "/" is.
• The LEFT function will let you extract the digit(s) before the "/".
• Dividing this by 100 will get you the decimal representation.
• =LEFT(text_cell,FIND("/",text_cell)-1)/100

If you have text with different denominators, you will need scale based on the denominator. 16 would need x1, 8 x2, 4 x4, 2 x8. After using FIND as above:

• The MATCH function will locate a value in an array.
• The INDEX function will select a value in an array.
• =LEFT(text_cell,FIND("/",text_cell)-1)*INDEX({1,2,4,8},MATCH(TEXTAFTER(text_cell,"/"),{"16","8","4","2"},0))/100

Alternately, you can convert the text fraction to a decimal value:

• =LEFT(text_cell.FIND("/",text_cell)/TEXTAFTER(text_cell,"/")

When the cell contains a decimal value (either originally or from the preceding conversion), the ROUNDUP function can provide the desired value:

• =ROUNDUP(decimal_cell*16,0)/100

Answer 2

Hi @Fiya

If you want to switch the text such as from "20 1/6" in cell A1 to "20.01", you may try the formula: =MID(A1,FIND(" ",A1),FIND("/",A1)-FIND(" ",A1))/100+MID(A1,1,FIND(" ",A1)).

If you want to switch the results of your formula, such as from "20.125" in cell H38 to "20.2", you may try the formula: =(H38-INT(H38))/(1/16)/10+INT(H38).

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---