where your data is in A1:A4 and B1=1000, this seemed to give the results you
want (change range references as needed):

=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(A1,0,0,ROW(A1:A4),1))<B1))+1-(INDEX(SUBTOTAL(9,OFFSET(A1,0,0,ROW(A1:A4),1)),MAT CH(TRUE,SUBTOTAL(9,OFFSET(A1,0,0,ROW(A1:A4),1))=B 1,0))-B1)/INDEX(A1:A4,MATCH(TRUE,SUBTOTAL(9,OFFSET(A1,0,0,RO W(A1:A4),1))=B1,0))

array entered w/Control+Shift+Enter (not just the enter key).

If you had another column w/ a running total of your data you could replace
SUBTOTAL(9,OFFSET(A1,0,0,ROW(A1:A4),1) with a range reference to your running
total.


"NWO" wrote:

Hello all.

Here is my business problem for which I am seeking a solution:

Desired outcome - generate a cycle time based on varible number of input
values marked agianst a set vaue. Example, I have a fixed value of 1,000,
and a string of consecutive monthly receipt values (from nearest month to
farthest month), say 300, 200, 300, 450. I need a solution to determine how
many times of these values go into 1,000, and the last value expressed as a
decimal, if applicable. So, using the above vlaue would generate a cyckle
time of 3.4444. That is, 300 =1 because 300 < 100, 200 = 2, because 300+200
< 1000, 300 = 3 because 300+200+300 < 1,000., and the difference between
1,000 and 800 divided by 450 = .4444. I have tried countif, sumif, arrays,
and I just can;'t seem to find a solution other than manually performimng the
calucation every time.

Any ideas to create a specialized formula or function to handle this task?

Thank you.

Mark :)