I'm always getting ideas and help from the board and I wanted to pass along this extention of a formula that I found here.
The original converted a decimal to the nearest 16th AND reduced it to lowest terms.
Referencing the value in cell E2
=IF(MOD(INT(16*(+E2-INT(E2)+0.0312)),16)=0,TEXT(E2,"#"),IF(MOD(INT(16*(+E2-INT(E2)+0.0312)),8)=0,TEXT(E2,"# 0/2"),IF(MOD(INT(16*(+E2-INT(E2)+0.0312)),4)=0,TEXT(E2,"# 0/4"),IF(MOD(INT(16*(+E2-INT(E2)+0.0312)),2)=0,TEXT(E2,"# 0/8"),TEXT(E2,"# 0/16")))))
I added the ability to take it to 32nds here:
=IF(MOD(INT(32*(+E2-INT(E2)+0.015625)),32)=0,TEXT(E2,"#"),IF(MOD(INT(32*(+E2-INT(E2)+0.015625)),16)=0,TEXT(E2,"# 0/2"),IF(MOD(INT(32*(+E2-INT(E2)+0.015625)),8)=0,TEXT(E2,"# 0/4"),IF(MOD(INT(32*(+E2-INT(E2)+0.015625)),4)=0,TEXT(E2,"# 0/8"),IF(MOD(INT(32*(+E2-INT(E2)+0.015625)),2)=0,TEXT(E2,"# 0/16"),TEXT(E2,"# 0/32"))))))
But I am unable to do 64ths in a single cell due to the 256 character limitation for formulas. I did make it work using a separate cell for rounding and then reducing to lowest terms.
=+E2-INT(E42)+0.0078125
and below it
=IF(MOD(INT(64*E3),64)=0,TEXT(E2,"#"),IF(MOD(INT(64*E3),32)=0,TEXT(E2,"#0/2"),IF(MOD(INT(64*E3),16)=0,TEXT(E2,"#0/4"),IF(MOD(INT(64*E3),8)=0,TEXT(E2,"# 0/8"),IF(MOD(INT(64*E3),4)=0,TEXT(E2,"# 0/16"),IF(MOD(INT(64*E3),2)=0,TEXT(E2,"# 0/32"),TEXT(E2,"# 0/64")))))))
If someone has the solution, Thanks. But my main purpose was to make what I have available for everyone. My work partners and I have banished that old conversion reference card to the dustbin of history and improved our speed and accuracy.
Thanks to the entire MrExcel bunch for making this forum available.
The original converted a decimal to the nearest 16th AND reduced it to lowest terms.
Referencing the value in cell E2
=IF(MOD(INT(16*(+E2-INT(E2)+0.0312)),16)=0,TEXT(E2,"#"),IF(MOD(INT(16*(+E2-INT(E2)+0.0312)),8)=0,TEXT(E2,"# 0/2"),IF(MOD(INT(16*(+E2-INT(E2)+0.0312)),4)=0,TEXT(E2,"# 0/4"),IF(MOD(INT(16*(+E2-INT(E2)+0.0312)),2)=0,TEXT(E2,"# 0/8"),TEXT(E2,"# 0/16")))))
I added the ability to take it to 32nds here:
=IF(MOD(INT(32*(+E2-INT(E2)+0.015625)),32)=0,TEXT(E2,"#"),IF(MOD(INT(32*(+E2-INT(E2)+0.015625)),16)=0,TEXT(E2,"# 0/2"),IF(MOD(INT(32*(+E2-INT(E2)+0.015625)),8)=0,TEXT(E2,"# 0/4"),IF(MOD(INT(32*(+E2-INT(E2)+0.015625)),4)=0,TEXT(E2,"# 0/8"),IF(MOD(INT(32*(+E2-INT(E2)+0.015625)),2)=0,TEXT(E2,"# 0/16"),TEXT(E2,"# 0/32"))))))
But I am unable to do 64ths in a single cell due to the 256 character limitation for formulas. I did make it work using a separate cell for rounding and then reducing to lowest terms.
=+E2-INT(E42)+0.0078125
and below it
=IF(MOD(INT(64*E3),64)=0,TEXT(E2,"#"),IF(MOD(INT(64*E3),32)=0,TEXT(E2,"#0/2"),IF(MOD(INT(64*E3),16)=0,TEXT(E2,"#0/4"),IF(MOD(INT(64*E3),8)=0,TEXT(E2,"# 0/8"),IF(MOD(INT(64*E3),4)=0,TEXT(E2,"# 0/16"),IF(MOD(INT(64*E3),2)=0,TEXT(E2,"# 0/32"),TEXT(E2,"# 0/64")))))))
If someone has the solution, Thanks. But my main purpose was to make what I have available for everyone. My work partners and I have banished that old conversion reference card to the dustbin of history and improved our speed and accuracy.
Thanks to the entire MrExcel bunch for making this forum available.