Count Until Sum of Counted = Value

[hockey390]

What I'm trying to accomplish is counting how many numbers from column A (starting at A2 and going down) it takes until it adds up to the number in B1. Twist of difficulty is I want a fraction.. So this would result in 3.5. 1+4+3+(half of 2). If A5 was = 3, answer would be 3.3.

	A	B
1		9
2	1
3	4
4	3
5	2
6	7
7	8
8	5
9	4

<tbody>
</tbody>

 

[NeonRedSharpie]

Function ADDUNTIL(rng As Range, value As Variant) As Variant

Dim count As Long


    For Each cell In rng
        ADDUNTIL = ADDUNTIL + 1
        count = count + cell.value
        Select Case count
            Case value
                MsgBox ADDUNTIL
                Exit Function
            Case Is < value
            Case Is > value
                ADDUNTIL = ADDUNTIL - 1
                ADDUNTIL = ADDUNTIL + 1 - ((count - value) / cell.value)
                Exit Function
        End Select
    Next cell


End Function

Here is a user defined function that will do what you want.

 

[Domenic]

Using native functions, maybe...

=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(A2:A9,,,ROW(A2:A9)-ROW(A2)+1))<=B1))+LOOKUP(0,(SUBTOTAL(9,OFFSET(A2:A9,,,ROW(A2:A9)-ROW(A2)+1))-A2:A9)-B1,(B1-(SUBTOTAL(9,OFFSET(A2:A9,,,ROW(A2:A9)-ROW(A2)+1))-A2:A9))/A2:A9)

Actually, in case B1 is greater than or equal the sum of values in A2:A9, try...

=IF(B1>SUM(A2:A9),"N/A",IF(B1=SUM(A2:A9),COUNT(A2:A9),SUMPRODUCT(--(SUBTOTAL(9,OFFSET(A2:A9,,,ROW(A2:A9)-ROW(A2)+1))<=B1))+LOOKUP(0,(SUBTOTAL(9,OFFSET(A2:A9,,,ROW(A2:A9)-ROW(A2)+1))-A2:A9)-B1,(B1-(SUBTOTAL(9,OFFSET(A2:A9,,,ROW(A2:A9)-ROW(A2)+1))-A2:A9))/A2:A9)))

Hope this helps!