Counting down formula

J

Jake

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
 
G

GS

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.
 
J

joeu2004

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

Jake

Jake said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top