MFengineer
New Member
- Joined
- Feb 25, 2022
- Messages
- 1
- Office Version
- 365
- Platform
- Windows
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.
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.
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.
Also asked here Replacing OFFSET to sum up to max value from specific cell
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.
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.
Also asked here Replacing OFFSET to sum up to max value from specific cell
Last edited by a moderator:
