Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a new function...
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 :) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a new function...
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 -- Cheers Nigel "NWO" wrote in message ... 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 :) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a new function...
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 :) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a new function...
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 :) "Nigel" wrote: 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 -- Cheers Nigel "NWO" wrote in message ... 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 :) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a new function...
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 :) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a new function...
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)),MAT CH(TRUE,SUBTOTAL(9,OFFSET(A1,0,0,ROW(A1:A4),1))=B 1,0))-B1)/INDEX(A1:A4,MATCH(TRUE,SUBTOTAL(9,OFFSET(A1,0,0,RO W(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. "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 :) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a new function...
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 :) |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a new function...
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 :) |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a new function...
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 :) "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 :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
not sure how to create this function | Excel Worksheet Functions | |||
Trying to create a certain function | Excel Worksheet Functions | |||
How do I create a function into function list? | Excel Programming | |||
where or how do i create a function that ive been given? | Excel Programming | |||
How do I create a sub function | Excel Programming |