Trying to calculate a cycle time.

Use the end pending for a given month, say June.

June end pending value is 1000.

What I am trying to do is take a count of each months receipts, starting
with June receipts, and working backwords for each month until the sum of the
receipts either equals or exceeds the end pending value, as illustrated in
the below example. Very seldon does the last months receipts equal a whole
number, so special attention is needed to convert this value to a decimal, as
expressed in the below e-mail. The outcome of this calcualtion is the cycle
time. Ccycle time, alternatively worded, is defined as the numebr of whole
months receipts that divide into the end pending number, with the exception
of the last month being calculated as a decimal.

Hope this helps.

Mark :)




"Gary''s Student" wrote:

This is just an example:

1. put monthly receipt values in column A
2. put the target (fixed) value in cell B1
3. enter and run this macro:


Sub Macro1()
Sum = 0
target = Cells(1, 2).Value
For i = 1 To 65536
Sum = Sum + Cells(i, 1).Value
If Sum = target Then Exit For
denom = Sum
If i = 65535 Then Exit Sub
Next
j = i - 1
result = (target - denom) / Cells(i, 1).Value
Cells(1, 3).Value = j + result
End Sub


It will perform the calculation and enter the result in cell C1. If you are
not familiar with macros, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm




--
Gary's Student


"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 :)