Oh, I forgot to mention, and I don't want to push things, but how would I
code a condition into the macro where the End Pending is high, and there is
not enough month's worth of receipt data to complete fthe fomrula. Ex: End
Pending = 1000, but only have three months of receipts (say, 300,200,400,
which sums to 900) - can an error msg be generated to indicate that not
enough data is presnet to calculate the cycle time? This condition is rare,
but can occur.

Thank you again.

Mark :)

"NWO" wrote:

Excellent - works perfect.

How can I adjust the macro so it will work with data cells reading across in
a row instead of down a column, and with column headings present in row 1?

Thank you again - you understood my questionable explanation of the problem
perfectly!

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