FORMULA HELP! Find all values in column at random rows and perform calcutlation.

P

Phillip Vong

ex.
A
1
2
3 3.9%
4
5
6 -2.7%
7
8
9 6.5%
10
11
12 1.2%
..
..
..
1000 Total of formula here.

I don't know what row a % number will show up on col A but it is before row
1000. I would like row 1000 under column A to have a formula that says
look for any row in column A and if you find a number, add 1 to that number
and then find the next one and add 1 to that number and then multiply those
two together and then find the next one add 1 then multiply to the last
set...etc.

Bascially, A100 would have the formula (1+A3)*(1+A6)*(1+A9)*(1+A12)...etc.
I just don't know what row a number will show up and I don't want to hand
type each little cell in A1000.

Is there a formual that will look at the whole colum and then do the math as
it finds a %? Thanks.

Phil
 
T

Tom Hutchins

I don't have a fancy formula (yet!) for your cell A1000 (probably needs an
array formula), but here is another approach:

In cell B1, enter: =IF(LEN(A1)>0,A1,1)
In cell B2, enter: =IF(LEN(A2)>0,(1+A2)*B1,B1)
Copy B2 down through B1000.

Hope this helps,

Hutch
 
I

ImpulseBlue

Enter the following as an array formula in your cell A1000. if not
familiar search excel help for array formula.

=PRODUCT(IF(A1:A999<>"",1+A1:A999,""))

Hope this helps.
 
Top