INT Function to Include Sum Function

[scott11]

Hello,

I have the following formula in column C: =INT(A2/12)&"' "&MOD(A2,12)&"""" which returns a value of 8' 8.75". I have several rows with this formula and at the end I would like to sum all the results to get the total feet and inches. Is this possible?

 

[Fluff]

Why not just sum the values in col A & then use your formula to convert it?

 

[scott11]

Why not just sum the values in col A & then use your formula to convert it?

Because that would make too much sense and my life easier...? Lol, thanks Fluff!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[scott11]

You're welcome & thanks for the feedback.

Is there a way to get fractions instead of decimals with that above formula by chance?

 

[Fluff]

Not really, you could use something like

=INT(A2/12)&"' "&INT(MOD(A2,12))&" "&INT(MOD(A2,1)*4)&"/4"&""""

but it won't be overly accurate.

+Fluff 1.xlsm
	A	B	C
1
2	104.6	3/5	8' 8 2/4"
3	104.7	7/10	8' 8 2/4"
4	104.5	1/2	8' 8 2/4"
5	104.4	2/5	8' 8 1/4"
Master

Cell Formulas
Range	Formula
B2:B5	B2	=MOD(A2,1)
C2:C5	C2	=INT(A2/12)&"' "&INT(MOD(A2,12))&" "&INT(MOD(A2,1)*4)&"/4"&""""

 

[Eric W]

You could just use a custom number format of

# ??/12

No formula needed.

 

[jtakw]

Hi,

A few suggestions/options for you to consider:

Book3.xlsx
	A	B	C	D	E	F	G	H	I
1
2	104.75		8' 8.75"	8' 8.75"	8' 8.75"	8' 8 3/4"	8' 8 3/4"	8' 8-3/4"	8' 8-8/10"
3	28.25		2' 4.25"	2' 4.25"	2' 4.25"	2' 4 1/4"	2' 4 1/4"	2' 4-1/4"	2' 4-3/10"
4	55.75		4' 7.75"	4' 7.75"	4' 7.75"	4' 7 3/4"	4' 7 3/4"	4' 7-3/4"	4' 7-8/10"
5	222.55		18' 6.55000000000001"	18' 6.55"	18' 6.55"	18' 6 5/9"	18' 6 11/20"	18' 6-11/20"	18' 6-6/10"
6	104.6		8' 8.59999999999999"	8' 8.60"	8' 8.6"	8' 8 3/5"	8' 8 3/5"	8' 8-3/5"	8' 8-6/10"
7	104.7		8' 8.7"	8' 8.70"	8' 8.7"	8' 8 5/7"	8' 8 7/10"	8' 8-7/10"	8' 8-7/10"
8	104.5		8' 8.5"	8' 8.50"	8' 8.5"	8' 8 1/2"	8' 8 1/2"	8' 8-1/2"	8' 8-5/10"
9	104.4		8' 8.40000000000001"	8' 8.40"	8' 8.4"	8' 8 2/5"	8' 8 2/5"	8' 8-2/5"	8' 8-4/10"
10
11	Total		34' 3.30000000000001"	34' 3.30"	34' 3.3"	34' 3 1/3"	34' 3 3/10"	34' 3-3/10"	34' 3-3/10"
12
13			The way you have now	Rounded w/Text	Rounded	As Fraction	As Fraction	As Fraction
Sheet746

Cell Formulas
Range	Formula
C2:C9	C2	=INT(A2/12)&"' "&MOD(A2,12)&""""
D2:D9	D2	=INT(A2/12)&"' "&TEXT(MOD(A2,12),"0.00")&""""
E2:E9	E2	=INT(A2/12)&"' "&ROUND(MOD(A2,12),2)&""""
F2:F9	F2	=INT(A2/12)&"' "&TEXT(MOD(A2,12),"0 #/#")&""""
G2:G9	G2	=INT(A2/12)&"' "&TEXT(MOD(A2,12),"0 0/#0")&""""
H2:H9	H2	=INT(A2/12)&"' "&TEXT(MOD(A2,12),"##-##/##")&""""
I2:I9	I2	=INT(A2/12)&"' "&TEXT(MOD(A2,12),"##-##/10")&""""
C11	C11	=INT(SUM(A2:A5)/12)&"' "&MOD(SUM(A2:A5),12)&""""
D11	D11	=INT(SUM(A2:A5)/12)&"' "&TEXT(MOD(SUM(A2:A5),12),"0.00")&""""
E11	E11	=INT(SUM(A2:A5)/12)&"' "&ROUND(MOD(SUM(A2:A5),12),2)&""""
F11	F11	=INT(SUM(A2:A5)/12)&"' "&TEXT(MOD(SUM(A2:A5),12),"0 #/#")&""""
G11	G11	=INT(SUM(A2:A5)/12)&"' "&TEXT(MOD(SUM(A2:A5),12),"0 0/#0")&""""
H11	H11	=INT(SUM(A2:A5)/12)&"' "&TEXT(MOD(SUM(A2:A5),12),"##-##/##")&""""
I11	I11	=INT(SUM(A2:A5)/12)&"' "&TEXT(MOD(SUM(A2:A5),12),"##-##/10")&""""

Change the 10 at the end of my I Column formula to whatever fraction you desire (e.g. 8, 10, 12, 16, etc.)

@Eric W, I'm not sure if cell formatting would work, as the OP's results are Text.

 

[Eric W]

You wouldn't apply the cell formatting to the results of the formula, you apply it to the cell with the number in it:

Book1
	A	B
1
2	104.6	104 7/12
3	104.7	104 8/12
4	104.5	104 6/12
5	104.4	104 5/12
6
Master

Excel does its best to find the "closest" fraction with that denominator. It could be slightly above, below, or the same as the decimal equivalent, so if so you need to round up (or down) to the closest multiple of 1/12, you'd need a formula such as you suggested.

 

[Phuynh]

Imperial feet-inches, if you want to format proper number fraction part, the denominator like /2, /4, /8, /16, /32, /64, /128, /256, /512, etc. use MROUND() with TEXT() functions.
MROUND() round the number to the desired multiple, for example 1/64, and Excel TEXT() format will take care the rest.

The denominator format number of digits (##) should be equal or more than the multiple (64)

TEXT(MROUND(A2, 1/64),"# #/##")

My thoughts on using INT() function in formula to convert to, or from any unit and depending the case of usage, it is good with positive number greater 0, but not with negative number less than 0, INT() is not symmetrical when rounding, I would avoid it all cost!

Ex:

INT(9.8) = 9
INT(-9.8) = -10

As you can see, INT() rounds a number down to the nearest integer, therefor when it in formula for conversion it behaves different with negative number!
Sum up, INT() when use in formula to compute a positive number, like convert to 'feet-inches', it's OK, but beware when using with negative number like minus tolerance it's may be incorrect result!

My 2 cents,

Phh