CODEWITHSUNDEEP
excel
Nasr Cheaib
Nasr Cheaib

Reputation: 83

Round down to nearest usable fraction

Im working in a welding shop and I want to cut my steel beams to the nearest usable fraction (1/16). It has to round down because I can always fill the space with weld. For example, I have a beam that is 140.47 on the drawing and when I use an excel function I get it to output 140 1/2" The excel function is:

=TEXT(A1,"0"&IF(ABS(A1-ROUND(A1,0))>1/32," 0/"&CHOOSE(ROUND(MOD(A1,1)*16,0),16,8,16,4,16,8,16,2,16,8,16,4,16,8,16),""))&""""

How can I get it to round down and output 140 7/16?

Upvotes: 2

Views: 83

Answers (1)

Scott Craner
Scott Craner

Reputation: 152465

Use MROUND:

=MROUND(A1-(1/32),1/16)

Then format the output:

0 #/##

enter image description here

If you do not mind text as the final output:

=TEXT(MROUND(A1-(1/32),1/16),"0 #/##")

Upvotes: 3

Related Questions