Help with a formula

[sandra_m]

Hi,

I have a worksheet in which I need to make the some calculations, based on the values I have in column C.

Column C contains only numbers greater then 0, except for one row, that has a zero value (around 50 rows in total).

For a better exemplification, I will write down a sample, so I’ll make myself understood.
In this particular example column C contains values from row 11 until row 29: C11=2, C12=6, C13=14, C14=18, C15=24, C16=21, C17=27, C18=0, C19=30, C20=12, C21=10, C22=7, C23=6, C24=5, C25=1, C26=2, C27=2, C28=1, C29=1.

Column D has to contains also values from row 11 and because C10 is blank D11=C11. D12=D11+C12, D13=D12+C13 and so on until it reaches the zero value in C18. in this case, D18=0 and then the calculation has to start from the last row that contains a value in column C (in this example row 29) until it reaches the zero value in C18. So D29=C29 (because C30 is blank), D28=D29+C28, D27=D28+C27, D26=D27+C26, … ,D19=D20+C19.

I know you’ll say why the bother, because the sum is commutative, but the problem is that in column E I’ll have to make the same calculation, considering as a reference column D. So if for the first column even if I make the calculation from up to down and from down to up I will obtain the same final numbers (I am interested in the maximum before and after the zero, so I will have in my example 112 and 77), but in the 2nd calculation if I calculate only from up to down I will have 333 and 690 and if after zero I calculate from down to the zero I will have instead of 690 234, and this is the value that I need.

Does someone have any idea how I can solve this issue?

Thank you.

 

[gsistek]

It sounds like you're looking for the sum before the zero and the sum after the zero. If this is the case, try:
(Sum before the zero)

=SUMPRODUCT(--(ROW(C:C) < MATCH(0,C:C,0)),C:C)

<?XML:NAMESPACE PREFIX = MATCH(0,C /><MATCH(0,C:C,0)),C:C)< p>(Sum after the Zero)

=SUMPRODUCT(--(ROW(C:C) > MATCH(0,C:C,0)),C:C)

</MATCH(0,C:C,0)),C:C)<>