1

Base equation

(J3+x)/150

Multiply answer by x then divide all by 150 and Repeat

( ((J3+x)/150)x )/150
(( ( ((J3+x)/150)x )/150 )x)/150

Keep repeating until the answer to the last repeat is <=1. Sum all parts.

Perhaps seeing this visually will better explain it. enter image description here

Right now I've simply broken up the equation into several cells and then sum them but this is terribly inefficient.

A1: =(J3+x)/150
B1: =(((J3+x)/150)x)/150
C1: =(( (((J3+x)/150)x)/150 )x)/150
D1: =(( (( (((J3+x)/150)x)/150 )x)/150 )x)/150
E1: etc, etc
  • Can either J3 or x be negative? – fixer1234 Jan 6 '15 at 7:22
  • That would never happen. – deflime Jan 6 '15 at 7:23
  • This will need a VBA solution. FYI, x must be < 150 or there will never be a term <= 1. – fixer1234 Jan 6 '15 at 7:45
  • Correct, x is not suppose to go over 150. – deflime Jan 6 '15 at 7:47
  • Can I check I have this right? If, for example, J3 = 10,000 and x=20 then A1 = 66.8, B1 = 8.9, C1 = 1.19 and D1 = 0.16, so as that last value is < 1 you want to just sum those 4 values and get a result of 77.05 (approx)? – barry houdini Jan 6 '15 at 18:51
2

Put the following User Defined Function (UDF) in a standard module:

Public Function deflime(J3 As Variant, x As Variant) As Double
    deflime = 0
    result = (J3 + x) / 150
    For i = 1 To 9999
        deflime = deflime + result
        If result <= 1 Then
            Exit Function
        End If
        result = x * result / 150
    Next i
End Function

User Defined Functions (UDFs) are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the UDF from Excel:

=deflime(A1,B1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

Here is an example:

enter image description here

|improve this answer|||||
0

I see you have a VBA solution but it is possible to do this with formulas.

Assuming your "x" value is in J2 put this formula in J4 to give the number of iterations that will take you to <= 1 for the first time

=MATCH(TRUE,(J3+J2)/150^{1,2,3,4,5,6,7,8,9,10}*J2^{0,1,2,3,4,5,6,7,8,9}<=1,0)

and then this formula in J5 to get your final value

=SUMPRODUCT((J3+J2)/150^ROW(INDIRECT("1:"&J4))*J2^(ROW(INDIRECT("1:"&J4))-1))

That first formula limits you to 10 iterations but you could expand that if required.....or even amalgamate those two formulas in to one "megaformula"

in my tests those formulas gave me the same results as Gary's Student's UDF

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.