SUM of specific range WITHOUT using OFFSET function

[MFengineer]

Hello everyone, I am new to the board & thank you all in advance for the help.

I have data for the time it takes each step in a manufacturing process & each step has a unique name that never repeats, as seen below, pasted in A1 corner.

Steps	time	Start Point	Sum of Steps	Count
2800N1	5	2800N3	14	2
2800N2	8	Max Time
2800N3	4	20
2800N4	10
2800N5	10
2800N6	10

What I want to accomplish is have (2) inputs that I can change independently, which are, which step am I starting on (Start Point) & MAX time I have available, then return the total time up to that cell (Sum of Steps) without exceeding the max time & how many steps were completed (Count).

For example, as seen on table above, lets say I will be starting at "2800N3" (located in A4) & I only have 20 minutes (my given Max Time), & I want to know how many steps I will be able to complete in those 20 minutes and what is the actual total time (sum) up to that last completed step. In this case, with 20 minutes, I will complete steps 2800N3 & 2800N4, and the sum of those completed steps is 14, and the number of steps completed was 2.

Another example, Start Point: 2800N2, Max Time: 30, RESULTS: Sum of Steps: 22 & Count: 3, ie with 30 minutes we can complete 2800N2 thru 2800N4, which is 3 steps.

I was able to get this to work using a combination of match, index, subtotal and offset, however offset is a volatile function, and since I will have about 30 of these in the same cell (one for each mfg machine) it becomes really slow when changing values, so I would like to use something that in non-volatile in hopes the calculations are faster.

 

[bosco_yip]

Try,

Create 5 range name as per attachment

Then,

1] In F2, formula copied down :

=MATCH(MaxTime,INDEX(PROB(RowNumber,StartPoint:LastPoint/SUM(StartPoint:LastPoint),,RowNumber)*SUM(StartPoint:LastPoint),))

2] In E2, formula copied down :

=SUM(StartPoint:EndPoint)

 

[Peter Bartholomew]

If you have 365 with monthly update, the following produces results for the 'Sum of Steps'

= MAP(stepStart,timeAvailable,
      LAMBDA(stepSt,timeAv,
        LET(
            timeSlots, XLOOKUP(stepSt,Steps,Time):LastPoint,
            timeAccum, SCAN(0,timeSlots, LAMBDA(t,x,t+x)),
            XLOOKUP(timeAv,timeAccum,timeAccum,,-1)
         )
      )
   )

The core of the calculation is the three steps within the LET which return a single 'sum of timeslots'. Nested within MAP, the formula returns an entire column of results as a dynamic array. The formula for the number of steps is near identical, the exception being that XLOOKUP is replaced by an XMATCH. It is possible to combine the two calculations to return the entire array of results as one dynamic array, but that requires MAKEARRAY and adds to the technical complexity of the solution.

 

[p45cal]

What version of Excel? For 2007 and later:
In the attached, in cell E2 a complex formula:

=MATCH(C4,MMULT(IF(ROW(INDEX(B:B,MATCH(C2,A:A,0)):B7)>=TRANSPOSE(ROW(INDEX(B:B,MATCH(C2,A:A,0)):B7)),1,0),INDEX(B:B,MATCH(C2,A:A,0)):B7))

This might need to be array-entered with Crl+Shift+Enter.
I mention cell E2 first because the formula in cell D2 depends on it:

=INDEX(MMULT(IF(ROW(INDEX(B:B,MATCH(C2,A:A,0)):B7)>=TRANSPOSE(ROW(INDEX(B:B,MATCH(C2,A:A,0)):B7)),1,0),INDEX(B:B,MATCH(C2,A:A,0)):B7),E2)

That's it.


There's a lot of repeating stuff in those formulae. If you have Office365, no array entering needed, in cell E3 I've put:

=LET(a,INDEX(B:B,MATCH(C2,A:A,0)):B7,MATCH(C4,MMULT(IF(ROW(a)>=TRANSPOSE(ROW(a)),1,0),a)))

and cell D3:

=LET(a,INDEX(B:B,MATCH(C2,A:A,0)):B7,INDEX(MMULT(IF(ROW(a)>=TRANSPOSE(ROW(a)),1,0),a),E3))

The above 2 formulae use whole columns, but they don't need to. For example in cell E4:

=LET(a,INDEX(B2:B7,MATCH(C2,A2:A7,0)):B7,MATCH(C4,MMULT(IF(ROW(a)>=TRANSPOSE(ROW(a)),1,0),a)))

and in cell D4:

=LET(a,INDEX(B2:B7,MATCH(C2,A2:A7,0)):B7,INDEX(MMULT(IF(ROW(a)>=TRANSPOSE(ROW(a)),1,0),a),E4))

All of these formula require there to be numbers (even if they are zero) in the relevant range in column B.

What was your formula?