Cumulative hours/levels calculation

Discuss the spreadsheet application

Cumulative hours/levels calculation

Postby DTemp » Thu Jun 15, 2017 4:04 pm

I need to create probably some kind of pivot or lookup table, neither of which I've done in a long time. I'm having a hard time thinking about what function I actually need to use to do this.

The calculation is regarding a kind of linear process. The process has 12 main parts, each divided into 10 decimal sub-parts, 0.0, 0.1, 0.2, etc., up to 12.0. So it's actually 120 discrete sub-parts. If doing the complete process, it would start at 0.0 and proceed through each sub-part until it reaches 12.0. But that never happens. Each instance of the process has its own starting point, i.e., it may start in the middle, as in halfway through part 3.3, which I would represent as 3.35. The process doesn't jump around -- once started, it finishes the part that it's on and proceeds to the next higher level. In this case it would go straight to 3.4 and start on that.

Each sub-part takes a particular amount of time to complete. So I have a table with columns indicating A) the decimal sub-part in question; B) the number of hours required to complete that sub-part; and C), by calculation, the advancement in completing the decimal sub-part per hour invested (=the inverse of col. B).

So using the inputs of:
--The starting point for this instance
--How many hours the process has been running
I need the calculated answer of what sub-part this instance should be working on at the moment.

So, it started at sub-part 3.35 and ran for 10 hours. Sub-part 3.3 takes 2 hours, so this instance needs 1 hour to finish it as it's started in the middle of the sub-part. Sub-part 3.4 takes 4 hours, so that gets finished, we've spent 5h so far. Subpart 3.5 also takes 4h; it gets finished too, using 9h so far. Subpart 3.6 takes 2h, in 10h total it can do the first half of this level and finishes at level 3.65. That's the answer that I want it to return, 3.65. Input starting point 3.35 and hours 10, it makes the appropriate calculation from my tables and returns back 3.65

Guidance appreciated :D
OpenOffice 4.1.2 on Mac OS 10.11.6
DTemp
 
Posts: 5
Joined: Thu Jun 15, 2017 2:45 pm

Re: Cumulative hours/levels calculation

Postby MrProgrammer » Thu Jun 15, 2017 11:50 pm

DTemp wrote:So using the inputs of:
--The starting point for this instance
--How many hours the process has been running
I need the calculated answer of what sub-part this instance should be working on at the moment.
Conversion from Level to Hours can done with Piecewise Linear Interpolation (PLI). In Calc you do that with MATCH, OFFSET, and the formula given in the link. I've done the calculations (Yb-Ya)/(Xb-Xa) and (Xb-Xa)/(Yb-Ya) in a separate columns.

Convert from 3.35 to hours using PLI. Then add 10 to that value. Convert the sum back to levels using PLI with the inverse function.

Interpolate.ods
(9.06 KiB) Downloaded 5 times

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
OpenOffice 3.2.0 Build 9483 on Mac OS X 10.9.5.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 2944
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Cumulative hours/levels calculation

Postby DTemp » Mon Jun 19, 2017 6:41 pm

Thanks so much for your help MrProgrammer. But I can't say I understand how you approached it.

First, I'm not sure what your variables represent, X, Y and D. I imagine D may represent change, but I'm not sure change in what. Y seems to represent time, but I'm not sure if that's total accumulated time, time per unit, or what aspect of time. And X I'm not sure at all.

As for the conversion from levels to hours, for a level that takes two hours, there are two numbers I could imagine seeing, 2 (hours per level) and 0.5 (levels per hour). On your spreadsheet in D2, I see 0.8333 and I can't imagine what that number represents or how it is useful. However I do see that the other level that takes the same amount of time is represented in D5 with the same figure 0.8333, and that the two intervening levels that take twice as long to complete are represented by the number 1.6667, exactly in proportion. I do see that your formula produced the right result, but I still don't understand the thinking behind it well enough to duplicate it myself.

If you could enlighten my I'd be most appreciative. :) Thanks!
OpenOffice 4.1.2 on Mac OS 10.11.6
DTemp
 
Posts: 5
Joined: Thu Jun 15, 2017 2:45 pm

Re: Cumulative hours/levels calculation

Postby Villeroy » Mon Jun 19, 2017 7:33 pm

(Ya-Yb)/(Xb-Xa) =B2/0.1
B2 has a "duration" which is the difference between 2 times. Therefore (Ya-Yb) is B2 and (Xb-Xa) evaluates to the 0.1 constant.

=MATCH(J3;$A$2:$A$5) returns the position number where threshold J3 is exceeded in A2:A5. Requires ascending sort order in A2:A5.
=OFFSET($D$1;J4;0) returns a value at that position in column D. =INDEX($D$2:$D$5;J4) is equivalent (value at position J4 within D2:D5).
the other OFFSETs do the same for other columns and the other formulas calculate something I don't understand yet.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 23866
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Cumulative hours/levels calculation

Postby MrProgrammer » Tue Jun 20, 2017 4:47 am

DTemp wrote:I'm not sure what your variables represent, X, Y and D.
In columns F and G, L is the Level, D is the Duration (time). The linked article uses X and Y. The level is X, the duration is Y.

DTemp wrote:Y seems to represent time, but I'm not sure if that's total accumulated time, time per unit, or what aspect of time.
Column C is the accumulated time at the beginning of the level. The formulas in C calculate the accumulated durations in column B.

DTemp wrote:On your spreadsheet in D2, I see 0.8333 and I can't imagine what that number represents or how it is useful.
Read section 4. Times in cells in Ten concepts that every Calc user should know. B2 is 2 hours or 0.08333 day. B2 is B2/0.1 which is 0.8333. If 0.1 level takes 2 hours, 1.0 level at the same rate takes 20 hours or 0.8333 day.

Villeroy wrote:The other OFFSETs do the same for other columns.
We know the level (3.35) and want to determine a duration using D=00:00+0.8333*(L-3.3). To calculate that formula (shown in F2) we need three numbers, 00:00, 0.8333, and 3.3. These are obtained via OFFSET funcions in cells J7, J5, and J6. J8 is the calculation of the formula in D2.

Villeroy wrote:The other formulas calculate something I don't understand yet.
Once we've added 10 hours, the result need to be converted back to a level. Formulas in F convert level to duration. Formulas in G convert duration to level; they are obtained by solving the formula in F for L using simple algebra. Match shows we need to use the 4th row of the table for the correct formula. Converting from duration 11:00 to level is done with the formula shown in G5, L=3.6+1.2000*(D-10:00). The numbers needed for that are in J13, J11, and J12. Those values are used in J14.

DTemp wrote:If you could enlighten my I'd be most appreciative
It will be difficult to follow the calculations in the spreadsheet until you understand piecewise linear interpolation. If you find the explanation in the link I gave you a bit terse, there are no doubt dozens of YouTube videos on the subject.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
OpenOffice 3.2.0 Build 9483 on Mac OS X 10.9.5.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 2944
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA


Return to Calc

Who is online

Users browsing this forum: No registered users and 47 guests