Length feet and inches?

gg

Well-known Member
Joined
Nov 18, 2003
Messages
560
I am trying to enter heights and weights of people
and have it sum up the values. Weight is NO problem
but Height?

I want to enter it as 6'4", 5'9", 6'2" etc..
and have the column sum the total height

any ideas?
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
heights

Any chance you could have two columns for the height? One for feet and one for inches? Then you would only need to use the mod function in your sums for the two columns.
 
Upvote 0
Yes I can and will do that if I need. I just thought there may
be a simple excel format adjustment...
 
Upvote 0
How about this:
Product Master by sku.xls
EFGH
16'4"
25'9"
36'2"
4
518'3"
6
Sheet3


Code:
=INT((SUM(VALUE(LEFT(E1:E3,FIND("'",E1:E3,1)-1)))*12+SUM(VALUE(MID(E1:E3,FIND("'",E1:E3,1)+1,LEN(E1:E3)-FIND("'",E1:E3,1)-1))))/12)&"'"&MOD(SUM(VALUE(LEFT(E1:E3,FIND("'",E1:E3,1)-1)))*12+SUM(VALUE(MID(E1:E3,FIND("'",E1:E3,1)+1,LEN(E1:E3)-FIND("'",E1:E3,1)-1))),12)&""""

This is an array formula and needs to be entered with Ctrl+Shift+Enter not just Enter.
 
Upvote 0
This is really not hard, but requires a few tricks. Enter your heights as a 2 place decimal, so 4ft 6in would be entered as 4.06, 5ft 10in would be entered as 5.10, etc. Now, use this as your sum formula

=DOLLARFR(DOLLARDE(SUM(yourRange),12),12)

If you want, custom format your cells ##"ft".00"in." to display your entries and answer as 13 ft. 04 in.
NOTE:YOU NEED THE ANALYSIS TOOLPAK ADDIN INSTALLED.
The function DOLLARDE converts the sum to a decimal. The DOLLARFR converts it back. Just a small trick!! :biggrin:

HTH

lenze

If you search for DOLLARDE and my name you will find other examples of this, including VBA usage.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top