Solved

Simplified Excel Formula Needed

Posted on 2011-09-27
9
291 Views
Last Modified: 2012-05-12
I am looking for simplified formulas if available.

Range A1:A4 = Hours; Range B1:B4 = Minutes. I need to sum both ranges so that I get a xx hours and xx minutes. AutoSum does not work because the “h” and “m” that are added to each range making them nonnumeric values. My solution was: =VALUE(IF(LEN(A1)>2,LEFT(A1,2),LEFT(A1,1))) –which simply states IF A1 is has a character count greater than 2 return 2 characters from the left, otherwise return only 1 character from the left and make all results numeric values.

The goal is to sum the minutes column keeping them in standard 60 minute increments and adding the overflow to the sum of the hours. E.g. if the hours column = 1 and the minutes column = 90 the final result would be 2 hours and 30 minutes.

I am using Excel 2010
I attached screenshots of the data & formula view as well as the file (.XLSX).

Any suggestions on how I can simplify these formulas? Thanks!

Excel-Data.JPG
Excel-Formulas.JPG
Excel-Example.xlsx
0
Comment
Question by:Diverse IT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 50

Expert Comment

by:barry houdini
ID: 36712359
You can use SUBSTITUTE to get rid of the "h"s and "m"s, i.e. for A6 to give total hours

=SUMPRODUCT(SUBSTITUTE(A1:A4,"h","")+0)+INT(SUMPRODUCT(SUBSTITUTE(B1:B4,"m","")+0)/60)

and in B6 for total remaining minutes

=MOD(SUMPRODUCT(SUBSTITUTE(B1:B4,"m","")+0),60)

see attached

regards, barry
27344329.xlsx
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 36712378
Of course if you want a very simple way then it would be easier to use a custom format in A1:A4, i.e. custom format as

0"h"

and for minutes column 0"m"

Now if you put numbers in those columns they'll display as 34h, for example, if you enter 34....but because the cells ionly contain numbers you can use simple SUM functions

regards, barry
0
 
LVL 12

Expert Comment

by:telyni19
ID: 36712386
A better way to do it than text manipulation would be to set a custom number format to display the "h" and "m" in a way that allows Excel to understand the values as numbers and sum them normally. See attached for your example with the custom formatting.

For hours, I used this:
##"h";;0"h"

For minutes, I used this:
###"m";;0"m"

Then all you need are regular numeric functions.
For hours, take  the minutes subtotal divided by 60 and rounded to a whole number of hours and add it to the hours subtotal. For minutes, use the MOD function to return the remainder of minutes left over when you take the whole hours out. Excel-Example.xlsx
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Expert Comment

by:barry houdini
ID: 36712453
Hello telyni19,

That's similar to my second suggestion but I don't think you should be using ROUND because that will round to the nearest hour when it should always be rounded down, e.g. if you change the minutes in B2 to 6 instead of 43 then the total should go down by 37 minutes and become 13 hours 35 mins ....but with ROUND the hours are 14....you should use INT in place of ROUND, i.e. in A7

=A6+INT(B6/60)

regards, barry
0
 
LVL 12

Expert Comment

by:telyni19
ID: 36712465
Yes, Barry is right. INT is correct.

I made my suggestion before seeing any of the other responses.
0
 
LVL 25

Author Closing Comment

by:Diverse IT
ID: 36712543
@ALL: Wow my formulas were ridiculous in comparison. Hahaha Thank you both for your quick responses!

@barryhoudini: I am awarding points for your second post as this is the simplest & most accurate formula. Thanks again.
0
 
LVL 25

Author Comment

by:Diverse IT
ID: 36712588
FYI: For future members; because the live data is spread out throughout the worksheet I am using the following formulas for Hours & Minutes respectively based on barryhoudini solutions (http:#a36712359 and consolidating it in http:#a36712378):

=SUM(A1,A2,A3,A4,INT(B6/60)) for hours

=MOD(SUM(B1,B2,B3,B4),60) for minutes
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 36712625
Note that you don't have to separate out the cells in the SUM function, you can use

=SUM(A1:A4,INT(B6/60))

and

=MOD(SUM(B1:B4,60)

regards, barry
0
 
LVL 25

Author Comment

by:Diverse IT
ID: 36712845
Yes, I know…it was a poor example on my part...the example that I posted here is much different from the actual worksheet. In the actual worksheet the cells are randomly placed that need to be tallied - the range is non-existent there.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question