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

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

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

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

Thank you again Jerry.

OK, I typed in the code in the Excel VB editor and then saved as an Add-In,
gave the file a name, then exited. When I opend the work file, go to Insert
function, the CycleTime function is not listed. What am I doing wrong here?


"Jerry W. Lewis" wrote:

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

Tools|Add-Ins and check the box beside the entry for your Add-In. If your
Add-In is not in that list, you can use the Browse button to find it. This
will make the function available for use in any workbook.

If you want to use the function from a VBA project, then you need to set a
reference from the project (in VBA Editor Tools|References)

Jerry

"NWO" wrote:

Thank you again Jerry.

OK, I typed in the code in the Excel VB editor and then saved as an Add-In,
gave the file a name, then exited. When I opend the work file, go to Insert
function, the CycleTime function is not listed. What am I doing wrong here?


"Jerry W. Lewis" wrote:

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