Excel question on number values?

Question

On one colunm I have certain number values:

.45

.50

1

.95

.80

.75

on the next column for each row I want to divid that number by the one above it then the one above that until it has to divid by the value of 1 on the first colunm then there is where I want it to stop. I want a formula to do this automaticlly.

Answer 1

Assuming your data starts in A1:

=IF( OR(A2="", A2=1), 0, IF(B2=0, A2/A1, B2/A1))

This formula works from the bottom up.  So, for the data provided, the following will result:

0.45..... 1.111..... (which is: 0.45 / 0.50)

0.50..... 0.....  (no result, since next cell is 1.00, formula starts over)

1.00..... 0.987.....  (which is: 0.987 / 1)

0.95..... 0.987.....  (which is: 0.938 / 0.95)

0.80..... 0.938.....  (which is: 0.78 / 0.80)

0.78..... 0.....  (no result, because next cell is blank)

Answer 2

I have created a macro that performs this calculation automatically, however, when you stated you wanted to stop when it reached 1, you didn't say whether is was calculating from top to bottom, or bottom to top of the list, therefore, this workbook contains two sheets, one that counts up, one that counts down. Also, because the value in row 1 cannot be divided by anything above it, there is no value in column B, row 1.

Here's a link to the file so you may download it:

http://www.savefile.com/files/1739175