ExcelBanter - View Single Post

Jerry W. Lewis

Save it in an Add-In.

Jerry

"NWO" wrote:

Darn Jerry, that was quick - thank you.

Please bear with my ignorance, but how can I make this UDF work in different
workbooks by just using the function only. In other words, how can I make it
so I can treat the function just like any other Excel function (i. e.
=sumif(value1, value 2...valuen)) with out worrying about the underlying
code. The documentation isn't helping me so much.

Thank you again Jerry.

Mark :)
-------------

"Jerry W. Lewis" wrote:

Function CycleTime(receipts As Range, target As Double)
n = 0
Sum = 0
For Each c In receipts
If IsNumeric(c.Value) And Not IsEmpty(c.Value) Then ' count only
numeric cells
Sum = Sum + c.Value
If Sum target Then Exit For ' don't count the value that
causes target to be exceeded
n = n + 1
denom = Sum
last = c.Value
End If
Next c
CycleTime = n + (target - denom) / last
End Function

Jerry

"NWO" wrote:

Hello all.

(1) How can I convert the below routine to a UDF

(2) How can I run this routine using a sheet with about 30 different form
types, each with a string of monthly receipts? Guess what I'm asking is how
would I create a UDF in the form CYCYLETIME(INPUT REFERENCE RANGE, END
PENDING REFERENCE CELL) so I can repeat the use of the function across
several columns or rows nased on the UDFs input values.

Thank you.

Below posts refer.

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

Expand AllCollapse All