Formula to keep a running total

[DH888]

I'm trying to create a formula that keeps a running total.

So it will look in the cell to the left and if there is a value greater than zero it looks for the next cell above with a value greater than zero and adds the two together.

If there is no value greater than zero above it takes just the value to the left.

The problem I'm having currently is if either the cell to the left or the cell directly above is empty it leaves the cell empty even though there's a value in either the cell to the left or "A CELL" above just not the cell directly above.
it takes no value at all leaving the cell blank. I need it be smart enough to look at more than just the cell directly above.

[TABLE="class: grid, width: 500, align: right"]
<tbody>[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]P[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Daily Over/Short[/TD]
[TD="align: center"]MTD Cash [/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]-12[/TD]
[TD="align: right"]Formula goes here[/TD]
[/TR]
</tbody>[/TABLE]










More than just there actually but every cell in that column.
The cell rows & Columns are accurately labeled in the above sample.

I tried to create an AND/IF formula but don't know the correct syntax for the formula to work.

Thanks all

 

[philwojo]

Why is there not always a value in column-O?

If there is now over/short can't you just put in a zero, that would be an easy solution to this problem it seems.

 

[DH888]

The reason is because there's a formula in O column cells that adds cells to the left of column O and I don't want any cells AFTER the current day to show values until there's an actual value inputted in the cells to the left of O

In other words if it's the 4th of the month I want the cells in Column O & P from the 5th on to the end of the month to remain empty so I'm using this formula
=IF((E7+F7+I7+J7+M7+N7=0),"",E7+F7+I7+J7+M7+N7)
in column O cells

So if there is actually no over or short on a given day the value would be zero but that will be suppressed by the formula that's in the O cells.

If I try to just add P7+O8 I get a #VALUE error
Not sure how to fix that. I guess it's seeing the value in cell O8 as a non number since it's value is currently an empty set
Or something.
Not sure what excel sees in that cell but the cell above has a value of 3 so that wouldn't be causing an issue

 

[philwojo]

So why can't you list a zero instead of a empty cell if my question? Does it have to remain as empty?

Also why not use the SUM formula instead of adding all those like that.

Sum(E7:N7) would do the same think you are doing there and make it easier to maintain, imho.

 

[philwojo]

You can also try this:

=IF(O7="",P6,P6+O7)

Put that in P7 and then drag down as needed.

That is assuming you don't use a zero in column-O.

 

[jtakw]

Hi,

Is this what you want accomplished?

Excel 2010
	O	P
5	Daily Over/Short	MTD Cash
6	5	5
7	-2	3
8
9	-12	-9
Sheet1

Cell Formulas
Range	Formula
P6	=O6
P7	=IF(O7="","",SUM(LOOKUP(9.99999999999999E+307,P$6:P6),O7))

P7 formula copied down.

 

[DH888]

So why can't you list a zero instead of a empty cell if my question? Does it have to remain as empty?

Also why not use the SUM formula instead of adding all those like that.

Sum(E7:N7) would do the same think you are doing there and make it easier to maintain, imho.

It just looks much better with all rows below the current date remaining empty instead of a column of 30 zeros in two rows.
Besides, I KNOW excel is MUCH SMARTER than that and since I KNOW it's possible why WOULDN'T I do it the better way?

Never thought to try that because there's text data in columns between all the numbers I'm adding together. Turns out I CAN use a sum formula and excel just ignores the text data in the included cells. awesome tip

Using the sum formula still doesn't change the issue I'm having though I've still got a #VALUE ! error in the cumulative column. Obviously excel doesn't see the empty cell as actually empty but definitely not as a number either.

 

[DH888]

You can also try this:

=IF(O7="",P6,P6+O7)

Put that in P7 and then drag down as needed.

That is assuming you don't use a zero in column-O.

That ALMOST works. The only issue is that every day below the current day shows the cumulative total as of yesterday.
Again it just doesn't look good. It doesn't look professional and it just looks pretty sad really.

 

[DH888]

Hi,

Is this what you want accomplished?

Excel 2010

	O	P
Daily Over/Short	MTD Cash

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]-12[/TD]
[TD="align: right"]-9[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]P6[/TH]
[TD="align: left"]=O6[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]P7[/TH]
[TD="align: left"]=IF(O7="","",SUM(LOOKUP(9.99999999999999E+307,P$6:P6),O7))
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



P7 formula copied down.

Thanks jtakw I think this could possibly work but it's not quite there yet.

Can you tell me what this part does 9.99999999999999E+307
What happens when I just copy down your formula from P7 and also before row 7 is the cell the formula is in stays empty whether there's a value in column O or not
Also what does this part do
P$6:P6
btw The first row of the table with data is row 5 The headings are on row 3&4

Thanks

 

[jtakw]

My solution as posted in post #6 is based on your setup in your OP, if your data is different, you need to adjust the cell references, below is adjusted formula based on your latest description:

Excel 2010
	O	P
4	Daily Over/Short	MTD Cash
5	5	5
6	-2	3
7
8	-12	-9
Sheet1

Cell Formulas
Range	Formula
P5	=O5
P6	=IF(O6="","",SUM(LOOKUP(9.99999999999999E+307,P$5:P5),O6))

P5 would be your first row of data, the formula simply copies O5.
P6 formula would be copied all the way down to your last row of data.

The LOOKUP function combined with 9.999999999999999E+307 finds the last value in range, P$5:P5 is the range to look in, as the formula is copied down, P$5:P5 will expand, in my sample above, in cell P8, the range would expand to P$5:P7, try it out and you'll see for yourself.