Search code examples
exceluser-defined-functions

Calculating completion potential using in-cell formula in Excel

I am trying to define an in-cell formula to calculate a potential transaction completion rate in Excel. I'm having difficulty finding a solution and I'm hoping someone can provide some direction if not a solution.

Inputs: There will be two variables in adjacent columns:

  • x is the number of seconds needed to complete a single transaction
  • y is the number of seconds between transaction start times

Objective: Find the total transaction completion rate possible in 60 seconds comprised of the number of transactions that can be fully completed in 60 seconds plus the sum percentage of partially complete transactions. Results will always be positive but will approach zero as x and y increase.

Added challenges:

  • If y is greater than or equal to 60 seconds, only one transaction can start
  • If x is greater than 60 seconds, then each transaction is a partial-complete

The easiest way to explain what I am trying to do is through examples.

If the solution were drawn on a timeline, the timeline would look like this:

Example 1
let x = 30 seconds
let y = 10 seconds
let ti = a transaction where i is the transaction number

t1 starts @ 0 seconds, ends @ 30 seconds == first complete transaction
t2 starts @ 10 seconds, ends @ 40 seconds == second complete transaction
t3 starts @ 20 seconds, ends @ 50 seconds == third complete transaction
t4 starts @ 30 seconds, ends @ 60 seconds == fourth complete transaction
t5 starts @ 40 seconds, time expires @ 60 seconds == (60-40)/30 = 2/3 of a complete transaction
t6 starts @ 50 seconds, time expires @ 60 seconds == (60-50)/30 = 1/3 of a complete transaction

The total transaction completion rate is 1+1+1+1+2/3+1/3 = 5

enter image description here

Here are few other examples with various inputs.

Example 2
let x = 40 seconds
let y = 10 seconds
let ti = a transaction where i is the transaction number

t1 starts @ 0 seconds, ends @ 40 seconds == first complete transaction
t2 starts @ 10 seconds, ends @ 50 seconds == second complete transaction
t3 starts @ 20 seconds, ends @ 60 seconds == third complete transaction
t4 starts @ 30 seconds, time expires @ 60 seconds == (60-30)/40 = 3/4 of a complete transaction
t5 starts @ 40 seconds, time expires @ 60 seconds == (60-40)/40 = 1/2 of a complete transaction
t6 starts @ 50 seconds, time expires @ 60 seconds == (60-50)/40 = 1/4 of a complete transaction

The total transaction completion rate is 1+1+1+3/4+1/2+1/4 = 4.5

Example 3
let x = 70 seconds
let y = 60 seconds
let ti = a transaction where i is the transaction number

t1 starts @ 0 seconds, time expires @ 60 seconds == (60-0)/70 = 6/7 of a complete
no further transaction starts because y >= 60

Result: 6/7 = ~0.857 

Example 4
let x = 70 seconds
let y = 30 seconds
let ti = a transaction where i is the transaction number

t1 starts @ 0 seconds, time expires @ 60 seconds == (60-0)/70 = 6/7 of a complete
t2 starts @ 30 seconds, time expires @ 60 seconds == (60-30)/70 = 3/7 of a complete
no further transaction starts because y >= 60

Result: 9/7 = ~1.286


Example 4
let x = 10 seconds
let y = 60 seconds
let ti = a transaction where i is the transaction number

t1 starts @ 0 seconds, ends @ 10 seconds == first complete transaction
no further transaction starts because y >= 60

Result: 1

I feel there should be an elegant solution to this, but I haven't been able to find it.

I can calculate the total completed when x and y are less than 60...

complete = (60 - x)/y + 1

...but this excludes the partial-completes, and it doesn't work when y >= 60.

I prefer not to use VBA, but helper columns are acceptable if necessary.

All thoughts/suggestions are appreciated.

Thanks

Solution

  • Assuming no version constraints, as indicated by your tags, you can try:

    • Enter in some cell and the results will spill down

    Edited to correct for case where y not a perfect divisor of 60

    =BYROW(
    $A$2:$B$7,
    LAMBDA(arr,
        LET(
            start, SEQUENCE(
                ,
                CEILING(60 / INDEX(arr, 2), 1),
                0,
                INDEX(arr, 2)
            ),
            end, BYCOL(
                start + INDEX(arr, 1),
                LAMBDA(arr, MIN(arr, 60))
            ),
            SUM(end - start) / INDEX(arr, 1)
        )
    )
)

    enter image description here