cumulative summing

[nelg001]

Hi All,

I'd really appreciate some help with a problem that I just cannot solve

example

Col........A......B......C......D......E......F......G......H......I......J......K
Row1......2......6......4......2......4......3......0......0......5.....This row has random numbers
Row2......2.....8......12.....14....18.....21..#N/A..#N/A..??(26)...This row sums previous number (Row2)+number above (Row1)=cumulative total

problem... being what formula can I put in the ?? column to pick up the cumulative count?...

When there is nothing to add to the cumulative total, I am telling Row2 to = #N/A because I need to chart Row2 and I don't want to chart 0's or repeat numbers

....can anyone please offer a non-VB solution to 'picking-up' where the count was lost...?

Many thanks in advance

nelg

[zbor]

Try

=IF(B1 = 0, NA(), B1+A2)

[dredwolf]

maybe something like this (I am going to assume you mis-named rows and columns, but the idea is the same either way) :
in B2

=IF(B1=0,NA(),B1+A2)

Copy across

Hope this helps

EDI-
zbor beat me to it...lol

[nelg001]

Thanks for the ideas...I probably haven't clarified my problem very well...
With =IF(B1=0,NA(),B1+A2) the count won't restart from the number just before the first #N/A

Hopefully I've clarified my problem... being what formula can I put in the ?? column to pick up the cumulative count?...

Thanks again

[zbor]

Try this:

=IF(B1 = 0, NA(), B1+LOOKUP(99^99, $A$2:A2))

[nelg001]

OMG! that is awesome...can you teach me how the formula works please? LOOKUP(99^99, $A$2:A2))

I've never encountered the "99^99,$A$2:A2

You are a LEGEND! Thanks!

[zbor]

Sure...
LOOKUP(99^99, your array) will return you LAST NUMBER of your array IGNORING errors.

So from the array LOOKUP(99^99, {21, 8, 12, 14, #N/A, 18, 2, #N/A}) it will return 2
First part 99^99 only need to be big enough that it won't appear in your array therefore LOOKUP will search that number until the end and when it can't find it it will return last known number.

SUMing part is easy.