Combining Formulas - Production hours

[robrobby]

I've been modifying a sheet which works out the remaining business hours from a production run and gives me the end date and time, so that I can easily see when the current order is going to be completed.

At the moment the formulas to do this is spread across many cells, the formulas works great but it's very untidy as I have to hide the calculation columns.

What I would like is for all the formulas in each cell H5:N5 to be combined into one cell G5, But I do not have a clue where to begin.


https://www.dropbox.com/s/p04vdwlmn7jfxhy/ProductionHours.xlsx

Would anybody be able to help me out on this?

 

[Jonmo1]

Welcome to the board.

First of all, there's nothing wrong with splitting calculations across multiple cells.
Sometimes it's actually preferred, if some of the intermediate calculations are repeated in other formulas.
If it's just an annoyance, you can move the formulas over to say AA5:AG5.
Then have G5 refer to the final calculation (presumeably in AG5 - originally N5)
Perhaps even put the formulas on another Sheet to get them completely out of the way.


That said, many people here can't (or don't want to) access filesharing sites, so we can't see your document.
It may help to post each of the formulas in H5 through N5.

 

[robrobby]

Thanks for the quick reply and advice,

I did consider moving the formulas to another sheet, or hiding the columns, but I know the users who will be using this will either delete/insert rows, or try copying the formulas down without copying the hidden cells.

I've created a cut down version below. I would like the combined formula in G5 if possible

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

Morning start Time

Evening End Time

Total Work Hours Per Day

2

7:00

22:00

=B2-A2

3

4

Date of Check

Time of Check

Part Number

Qty

PPH

Remaining hours

End Date/Time

Combined Start Date Time

length

full workdays

part workdays

start+length

Calc1

Calc2

5

27/3/13

10:00

MyPart001

7680

140

=ROUNDUP(D5/E5,0)

=WORKDAY(H5,J5+N5,$S$5:$S$31)+M5

=A5+TIME(B5,0,0)

=F5/24

=INT(I5/$C$2)

=I5-(J5*$C$2)

=B5+K5

=IF(L5>$B$2,L5-$B$2+$A$2,L5)

=IF(L5>$B$2,1,0)

<tbody>
</tbody>

 

[Jonmo1]

In this case, I'm gonna have to strongly urge you to leave it as is.
There are so many repetitions of the same calculation, that if it's all combined into one formula the same calculation ends up being executed many times.
This is very detrimental to performance of your sheet.

I will however tell you how you can do it yourself if you must...

You have the representation of all the formulas there.
Begin with G5

=WORKDAY(H6,J6+N6,$S$6:$S$31)+M6

Take the reference to H6, just copy the formula in H6 (minus the = sign) and paste it into the G5 formula in place of the G5 reference.
so it becomes
=WORKDAY(A6+TIME(B6,0,0),J6+N6,$S$6:$S$31)+M6

You may need to add () around the part you paste in to ensure the order of math operations happen in the correct order.
So you might make that like this instead

=WORKDAY((A6+TIME(B6,0,0)),J6+N6,$S$6:$S$31)+M6

Then do J6

=WORKDAY((A6+TIME(B6,0,0)),J6+N6,$S$6:$S$31)+M6
becomes
=WORKDAY(A6+(TIME(B6,0,0)),(INT(I6/$C$3))+N6,$S$6:$S$31)+M6


Just continue repeating that until all references to H6:N6 are gone from G6

 

[Jonmo1]

Not only because of formula performance, but ease of reading.

Your resulting formula after combining it all into one, will be so long and convoluted it will be near impossible to read and understand what it's doing.
Keeping calculations seperated the way they currently are allows a representational display of what each part of the calculation is doing.
Much easier for reading/understanding and more importantly for troubleshooting.

 

[robrobby]

Many thanks, I can now see how to do it

I'll take your advice though, as you say, it can become very detrimental and hard to maintain.

Thanks for spending the time in helping me do this. Very apprectiated

Rob

 

[Jonmo1]

Glad to help, thanks for the feedback.


You've made a wise choice.

As far as users messing things up, I would say go ahead and put the formulas on another sheet and hide that sheet.
If that doesn't help, then "Educate" your users...lol.