mastertl

C7p6w.jpg

I'm trying to get the Cumulative Column to run a continuous total and I'm able to get it to work assuming there are no blank rows. As soon as there is a blank row, the formula goes to shits. I'd like both the Total and Cumulative column to return null cells if there are no numbers, but if there is a completely empty row, is there a way to make the Cumulative column search vertically in the column to continue adding?

My current formulas (I know they are not elegant, I suck...)

For Total: =IF(SUM(C3:E3)>0,SUM(C3:E3),"") [This would be for Cell F3]

For Cumulative: =IF(F3="","",IF(F3>0,(F3+G2),)) [This would be for Cell G3]

If I skip a line, I get the #VALUE! error. Any ideas?

rza49311

iTrader: (1)

sounds like you need to nest that inside an IF with an IsError test.

te3point5

Why don't you do:

in Cell G2: =sum($F$2:F2)

Then drag down

svtmike

The "" are what is causing you trouble.

Get rid of them and replace them with these simple formulas:

Total: =SUM(C2:E2) and fill down

Cumulative: =SUM(F$2:F2) and fill down

Go to Excel Options->Advanced, under the "Display Options for this Worksheet", uncheck "Show a zero in cells that have zero value".

Bearcat94

What they said.

Think of this way: If you have a blank row, the value for that day is "0". Your cumulative amount, including that day, is the previous cumulative amount + 0.

IOW - just let the cumulative amount tally on those blank rows.

1Louder

What they said too.

I think the #Value is coming from not setting a value if F3<=0:

IF(F3="","",IF(F3>0,(F3+G2),))

Because you are triggering that condition, which is leaving some null value in G6 and then you're trying to add it in G7. If you did this:

IF(F3="","",IF(F3>0,(F3+G2),0))

It would work, but you'd have a zero in the cell.

svtmike

What's happening is the "" used as the result of the IF() functions is setting up a string value in some of the cells, which the SUM() function doesn't handle -- it throws a #VALUE error instead.

I just went through an extensive redesign of a large spreadsheet to eliminate this very problem.

mastertl

The SUM formula worked perfectly. I think I was thinking too hard about how to make the table work. Its much simpler than what I originally had in mind.

I ended up using this in G2 and dragged down.

=IF(F2="","",SUM($F$2:F2))

The problem with unchecking the show zeroes option is that I'm not sure if it would show up when I send the spreadsheet out.

Y'all are some Excel pros

svtmike

That setting will stay with the spreadsheet, so it will work when you send it out.

1Louder

Another alternative to "show zeros" - If you ever want to get around that (or having to put strings in your cells), but don't want zeros showing up, what you can do is have your IF statements populate cells with a zero (or make it some bizarre number that would never happen, like -9999). And then set conditional formatting on the cell to turn the font white when the cell equals that value. It's not too much work and it's a good way to make unwanted place-holder data "disappear" on print outs.