• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Adding time

mithil1

Member
I am trying to find a formula that will help me add over time. So for example:

Week 1 has 40:36 (Colum A) and Week 2 has 40:36 (Col B) = Regular hours will be 80 (Colum C) and overtime will be 1 hour 12 mins (Colum D). And finally I want to drag the formula down for all.

2nd situation

either week 1 or 2 will have more than 40. So, lets say week 1 has 35 and week 2 has 40.35 then over time is 0:35 mins.
and another time week 1 will have 40:25 and Week 2 will have 38.

so basically i am trying to do is if week 1 is more than 40:00 than add OT from week 1 with OT from week 2 if any.

Can someone please help?
 

Pete Wright

New Member
I have prepared a worksheet that probably does, what you want, but not with a single formula.
The worksheet uses some helper columns to achieve the desired calculations.

Just tell me, if the result (OT Calculation) is what you need.
If it is, the next step would be to combine the helper cells with the calculation cells.

67329

kind regards
Pete
 

Attachments

Peter Bartholomew

Well-Known Member
This is Pete Wright's solution except that I named every column of data and then took the formula from the column and inserted it in the 'Refers to' box of the name to produce a Named Formula. Once that is done, the helper table can be deleted without altering the result.
67338

Note: The results are array formulas but, since I use Office 365, a single cell formula is only an array formula that happens to represent a 1x1 array.
 

Attachments

Last edited:

Peter Bartholomew

Well-Known Member
Having seen such an impressively documented example of traditional Excel techniques, I thought I might do the same for an array solution using Names. I have combined all weeks (only 2 in the example) into the columns of an array so I have only two formula cells irrespective of the number of rows or weeks covered.
67356
My reason for posting is to demonstrate that Excel will support radically different ways of working. As to which is the simpler, I will leave that to others to determine ;)
 

Attachments

Pete Wright

New Member
Wow, this is new to me. Never heard of Arrays in Excel before.
Now I am very curious, could you please be so kind and explain this to me?

Let me sum up your sheet:
hours =24*Sheet1!$A$5:$B$7
-> this becomes an Array containing all the times converted to a decimal number {40.6, 40.6, 35, 40.58333, 40.41667, 38} - seems logical
But the syntax is a little bit confusing: First, you calculate the product of 24 and a Range - Does that mean that the single values of the Array hours are calculated separately like in Conditional Formatting?
e.g.: 24*A5:B7 = 24*A5, 24*B5, 24*A6, 24*B6, 24*A7, 24*B7

And I absolutely don't get Overtime = IF( hours > 40, hours - 40, 0 ) and Regular_h = IF( hours > 40, 40, hours )
I know that one can store values in a Name, but complete Formulas? And a Formula containing an Array?
I'm lost here.

I do know some languages like JS, PHP and JAVA and their syntax is quite understandable, but Excel...

It even gets more complicated:
= MMULT(Overtime, {1;1}) / 24
-> MMULT(<Overtime-Array>, <1x1 Array>)
->
| 40.6 40.6 |
| 35 40.583 | X | 1 1 |
| 40.417 38 |

mmmhm, I should take a look at my old math books...
 

Peter Bartholomew

Well-Known Member
@Pete Wright

Array formula have been around for longer than Excel but they are not really an End-User-Computing technique and their use has not been optimised in the same way that single-cell relative referencing has. Also, they are not discoverable in the sense that one might select the region for the results and commit the formula using Ctrl+Shift+Enter, just to see what happens.

Reference material might include Chip Pearson's legacy
and Mike Girvin

In the most recent versions of Office 365 everything has changed and now array formulas are the default (no more CSE). A formula within a single cell can now return an array result which will display by 'spilling' across adjacent blank cells. Either way, all calculation within Excel is based upon 2D arrays; a single cell simply gives rise to a very small array.

You are correct that multiplying a 2D range by a constant will multiply each value in the range by the constant (24 in this case) and return the result as an array. Similarly, the formula
= IF( hours > 40, hours - 40, 0 )
tests each element of 'hours' against 40 and then either subtracts 40 or returns 0 as the elements of the output array.

As for defined Names, every name refers to a formula. A formula of the form '=$A$5:$C$7' returns an absolute range reference but any valid formula may be given a Name and will, in general, return an array. Simple examples include named constants such as '= 24' (the number of hours in a day) or '={1,1}' (a row array comprising two 1s) and '={1;1}' (a column array similarly comprising two 1s). When Excel encounters a Name within a calculation, it simply replaces it by the formula from its 'refers to' text and evaluates that.

The MMULT function is an overly mathematical function that I have used to sum individual rows of a 2D array. I have an add-in that allows me to use
= SUMROWS( Overtime ) / 24
but, in general, Excel does not support operations involving 2D arrays at all well because it is assumed the user will want to manually select rows one by one for summation, MMULT performs much the same calculation as multiple SUMPRODUCTs but with the first array transposed and it gives an array output.

I hope this helps. It is the way I have worked for years but, on this forum, it probably causes more confusion than enlightenment overall.
 
Top