Foot/Inch decimal to fraction conversion In Formula

[TheTiredEngineer]

I have this bit of formula that converts a decimal form of feet/inches to feet and inches, but it doesnt want to convert 12" to 1 ft for some odd reason. Can anyone tell me what Im doing wrong here?

So like 80.999 ft should be 81 ft not 80ft, 12".

ROUNDDOWN(dia,0)&"' "&ROUND(MOD(dia,1)*12,0)&"""")

 

[Joe4]

If your number is "ROUNDDOWN" with a 0 in the second argument, the value that it returns can NEVER be bigger than the integer portion of your formula.
If you start with 80.999, the ROUNDDOWN function will not return anything bigger than 80 in that formula.
So I think we need to check, and if the inches calculation returns 12, to add one to our feet.

Since you are using Excel 365, we can use the LET function to shorten this a little, like this:

=LET(i,ROUND(MOD(dia,1)*12,0),IF(i=12,ROUNDDOWN(dia,0)+1&"' 0" & """",ROUNDDOWN(dia,0)&"' "&i&""""))

 

[Jeffrey Mahoney]

This is my first attempt
=LET(REM,ROUNDUP((dia-INT(dia))*12,1),I,INT(dia)+IF(REM>=12,1,0),I&IF(REM=12,"","' "&(ROUNDUP((dia-INT(dia))*12,2)&CHAR(34))))

 

[TheTiredEngineer]

If your number is "ROUNDDOWN" with a 0 in the second argument, the value that it returns can NEVER be bigger than the integer portion of your formula.
If you start with 80.999, the ROUNDDOWN function will not return anything bigger than 80 in that formula.
So I think we need to check, and if the inches calculation returns 12, to add one to our feet.

Since you are using Excel 365, we can use the LET function to shorten this a little, like this:

=LET(i,ROUND(MOD(dia,1)*12,0),IF(i=12,ROUNDDOWN(dia,0)+1&"' 0" & """",ROUNDDOWN(dia,0)&"' "&i&""""))

Perfect! That worked exactly like I wanted. I guess Im trying to understand the code now, so whats the Let doing?

 

[Jeffrey Mahoney]

LET helps you create variables that store Arrays, strings, numbers and such so you can use them multiple times in your formula. In Joe's formula "i" is used to store the value of the remainder after the whole number rounded. This prevents the same calculation from being performed multiple times

 

[HighAndWilder]

LET helps you create variables that store Arrays, strings, numbers and such so you can use them multiple times in your formula. In Joe's formula "i" is used to store the value of the remainder after the whole number rounded. This prevents the same calculation from being performed multiple times

LET is a brilliant addition to Excel.

 

[Joe4]

Perfect! That worked exactly like I wanted. I guess Im trying to understand the code now, so whats the Let doing?

See here for a detailed description on the LET function (note: Microsoft has articles like these for all Excel functions).

LET function - Microsoft Support

support.microsoft.com

 

[Jeffrey Mahoney]

My suggestion when trying to use the LET function. Make your formula with all the raw calculations, then once you have tested it and resolved all the possibilities you can add in the variables. You know how you can edit a formula, highlight a part of the formula, and Excel will show you the result above it? Well, Excel won't show you the result if it includes a variable.

The other day I learned another drawback. LET stores Ranges as Arrays like {12.3;1.5;0.14,9.98}. So, functions like Countif use ranges and won't work right if you store a range into a variable.

Other than that, I think LET is very useful.

 

[Phuynh]

Why not use MROUND, rounded value to the desired multiple, for example 1/16 or 1/64 etc., before ROUND & MOD functions,
this solve issue with end up 12"

Original Excel snippet

ROUNDDOWN(dia,0)&"' "&ROUND(MOD(dia,1)*12,0)&"""")

New snippet with MROUND wrap around "dia" with round-off 1/64

ROUNDDOWN(MROUND(dia,1/64),0)&"' "&ROUND(MOD(MROUND(dia,1/64),1)*12,0)&"""")

Phh

 

[Scott Huish]

My suggestion when trying to use the LET function. Make your formula with all the raw calculations, then once you have tested it and resolved all the possibilities you can add in the variables. You know how you can edit a formula, highlight a part of the formula, and Excel will show you the result above it? Well, Excel won't show you the result if it includes a variable.

The other day I learned another drawback. LET stores Ranges as Arrays like {12.3;1.5;0.14,9.98}. So, functions like Countif use ranges and won't work right if you store a range into a variable.

Other than that, I think LET is very useful.

It does for me

Book1
	A	B	C	D
1	7			2
2	9
3	14
4	26
5	14
Sheet1

Cell Formulas
Range	Formula
D1	D1	=LET(a,A:A,COUNTIF(a,14))