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

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

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

"Phillip Vong" wrote:

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

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.

Phillip Vong wrote:
> 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