How do I write a formula to calculate how many iterations it will take to
reduce a number by a given percentage until that result is less than 1.

Example, how many iterations will it take to reduce 100 by 0.1% until the
value is less than 1

100, 99.9, 98.001, ..... 0.99999

Thanks

Jake submitted this idea :
How do I write a formula to calculate how many iterations it will take to
reduce a number by a given percentage until that result is less than 1.

Example, how many iterations will it take to reduce 100 by 0.1% until the
value is less than 1

100, 99.9, 98.001, ..... 0.99999

Thanks

Using a VBA function, it takes 4,603 iterations to reduce 100 by 0.1%
until the remainder is less than 1. Here's my take on your posted
results:

99.9, 99.8001, 99.7002999 ..., 0.9999867159327194

Function GetNumOfIterations(ByVal Number As Double, _
ReduceBy As Double) As Long
Do
Number = (Number - (Number * ReduceBy))
GetNumOfIterations = GetNumOfIterations + 1
Loop Until Number < 1
End Function

To use the function in a worksheet, copy it to a standard module in the
workbook where it's to be used, and enter the following formula in the
cell you want the result:

=GetNumOfIterations(100,0.1%)

The formula can be used on any sheet within the workbook.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Mar 10, 7:07*pm, "Jake" wrote:
how many iterations will it take to reduce 100 by 0.1%
until the value is less than 1

=ROUNDUP(NPER(-0.1%,0,-100,1),0)

There is a very small chance that that will reduce exactly to 1
instead of less one. If you are concerned, you could do the
following:

=ROUNDUP(NPER(-0.1%,0,-100,1),0)
+(FV(-0.1%,ROUNDUP(NPER(-0.1%,0,-100,1),0),0,-100)=1)

"Jake" wrote in message
...
How do I write a formula to calculate how many iterations it will take to
reduce a number by a given percentage until that result is less than 1.

Example, how many iterations will it take to reduce 100 by 0.1% until the
value is less than 1

100, 99.9, 98.001, ..... 0.99999

Thanks

Thanks guys, that works for me.