Create a new function...

[Guest]

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

 

[Nigel]

I think I might benefit from a clearer definition of the problem. Can you
express the function required in a more generic form, the examples raises
too many what if questions for me. Sorry

 

[Guest]

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

 

[Guest]

Hi Nigel.

Not a problem.

We have a pending value of cases and we wish to ascertain a calculated cycle
time based on incoming receipts per month to figure out approximantely how
long the maximum processing time, expressed in months, should take for each
case. So we take the last month's end pending value, say June, and using
this value as a comparision factor, take each month's receipt value, starting
with June. If the June receipt value is less than the pending value, then
that equals one, we then take the month of May's receipt value and add this
to June's receipt value, if the total value is less than the June End
Penfing, then that equals 2, and so on unitl the last value in the string of
values brings the total to equal or exceed the June End Pedning value in
which case we express this last value as a percent, as illustrated in the
below example. The cycle time is expressed in months. Hope this helps to
clarify the problem.

Thank you.

Mark

 

[Guest]

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

 

[Guest]

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)),MATCH(TRUE,SUBTOTAL(9,OFFSET(A1,0,0,ROW(A1:A4),1))>=B1,0))-B1)/INDEX(A1:A4,MATCH(TRUE,SUBTOTAL(9,OFFSET(A1,0,0,ROW(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.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Thank you Gary's Student.

(1) How can I convert to a UDF (have never done this before - have read the
documentation, but I guess I need more hand holding in terms of steps)...

(2) How can I run this routine using a sheet with about 30 different form
types, each with a string of monthly receipts - use an array?

Thank you.

Mark