# How can the SUM be repeated in the equation until answer is less than or equal to 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. 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

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

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