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

 

[DH888]

jtakw that works perfectly thank you so much for your help.

 

[jtakw]

You're welcome.

 

[DH888]

<gs id="0eef02bb-e116-4732-b8c7-b0ddb8c71da5" ginger_software_uiphraseguid="886c56e2-33c0-46ca-ac3b-4e5865e78908" class="GINGER_SOFTWARE_mark"><gs id="ae5a35e5-6391-46ad-a000-96cf13fd3460" ginger_software_uiphraseguid="a60f0aa6-6967-4a75-b4d8-aab7c6d846f5" class="GINGER_SOFTWARE_mark">can</gs></gs> you explain this to <gs id="3825b286-7a61-46ee-abec-f6551d24b9d0" ginger_software_uiphraseguid="886c56e2-33c0-46ca-ac3b-4e5865e78908" class="GINGER_SOFTWARE_mark"><gs id="48b6fb11-7037-4625-9d70-96d56feec514" ginger_software_uiphraseguid="a60f0aa6-6967-4a75-b4d8-aab7c6d846f5" class="GINGER_SOFTWARE_mark">me</gs></gs>
9.999999999999999E+307
What EXACTLY does it say?

Wouldn't a MUCH smaller number work the same?
I'm not being a <gs id="396f813b-7298-41a1-a110-6999161e3406" ginger_software_uiphraseguid="d7688dff-1728-4366-b505-c6c197c0a75a" class="GINGER_SOFTWARE_mark">smartass I</gs>'m trying to understand.

 

[jtakw]

<gs class="GINGER_SOFTWARE_mark" id="0eef02bb-e116-4732-b8c7-b0ddb8c71da5" ginger_software_uiphraseguid="886c56e2-33c0-46ca-ac3b-4e5865e78908"><gs class="GINGER_SOFTWARE_mark" id="ae5a35e5-6391-46ad-a000-96cf13fd3460" ginger_software_uiphraseguid="a60f0aa6-6967-4a75-b4d8-aab7c6d846f5">can</gs></gs> you explain this to <gs class="GINGER_SOFTWARE_mark" id="3825b286-7a61-46ee-abec-f6551d24b9d0" ginger_software_uiphraseguid="886c56e2-33c0-46ca-ac3b-4e5865e78908"><gs class="GINGER_SOFTWARE_mark" id="48b6fb11-7037-4625-9d70-96d56feec514" ginger_software_uiphraseguid="a60f0aa6-6967-4a75-b4d8-aab7c6d846f5">me</gs></gs>
9.999999999999999E+307
What EXACTLY does it say?

Wouldn't a MUCH smaller number work the same?
I'm not being a <gs class="GINGER_SOFTWARE_mark" id="396f813b-7298-41a1-a110-6999161e3406" ginger_software_uiphraseguid="d7688dff-1728-4366-b505-c6c197c0a75a">smartass I</gs>'m trying to understand.

No problem at all.

That number is commonly referred to as BIG NUM in Excel, supposedly the largest number Excel can handle, and yes, it's a huge number.
A smaller number can definitely work if you KNOW that your data will never exceed it, for example if you KNOW your data in range will NEVER exceed 100, then even 101 will work; however, it's common practice in Excel to use the BIG NUM because it's almost guaranteed you won't have a larger number.

The logic behind combining LOOKUP with the BIG NUM is such that LOOKUP tries to find that number, and since it CAN'T find it, it returns the last value in range.
The following shows a few samples of how it works:

Book1
	A	B	C	D	E	F
1	1		3	3	99	99
2	5
3
4	99
5
6	200
7
8	3
9
10
Sheet1

Cell Formulas
Range	Formula
C1	=LOOKUP(9.99999999999999E+307,A1:A10)
D1	=LOOKUP(201,A1:A10)
E1	=LOOKUP(100,A1:A10)
F1	=LOOKUP(99,A1:A10)

Hope that helps you.

 

[DH888]

Hey that was a great little tutorial thanks a lot.

 

[jtakw]

You're welcome, happy to help.

 

[JumpingCrab]

If you just need a running total across a column with different values (some being text), you can just us the sum function as follows:
(assume the values are in column A and the running total is in column B)

Column B
=A1
=B1+sum(A2)
=B2+sum(A3)
=B3+sum(A4)
etc...

The sum function ignores anything that does not conform to a number. This way you will not have the #VALUE errors in the running total column.

Hope this helps.

 

[jtakw]

If you just need a running total across a column with different values (some being text), you can just us the sum function as follows:
(assume the values are in column A and the running total is in column B)

Column B
=A1
=B1+sum(A2)
=B2+sum(A3)
=B3+sum(A4)
etc...

The sum function ignores anything that does not conform to a number. This way you will not have the #VALUE errors in the running total column.

Hope this helps.

Read Post #1 , Post #7 , and Post #8 , OP will have BLANK rows that he/she wants to remain BLANK.

 

[JumpingCrab]

Ah, I didn't catch that. So something like this could work as well, I guess.
Gives blank rows in the running total for all rows not containing numbers. Any row with text will also give a blank running total.
You can drag the formula down for each row you add.

Column B
=IF(ISNUMBER(A18),A18,"")
=IF(ISNUMBER(A19),SUM(A$18:A19),"")
=IF(ISNUMBER(A20),SUM(A$18:A20),"")
=IF(ISNUMBER(A21),SUM(A$18:A21),"")
=IF(ISNUMBER(A22),SUM(A$18:A22),"")
=IF(ISNUMBER(A23),SUM(A$18:A23),"")
=IF(ISNUMBER(A24),SUM(A$18:A24),"")
=IF(ISNUMBER(A25),SUM(A$18:A25),"")
=IF(ISNUMBER(A26),SUM(A$18:A26),"")

JL

 

[DH888]

Ah, I didn't catch that. So something like this could work as well, I guess.
Gives blank rows in the running total for all rows not containing numbers. Any row with text will also give a blank running total.
You can drag the formula down for each row you add.

Column B
=IF(ISNUMBER(A18),A18,"")
=IF(ISNUMBER(A19),SUM(A$18:A19),"")
=IF(ISNUMBER(A20),SUM(A$18:A20),"")
=IF(ISNUMBER(A21),SUM(A$18:A21),"")
=IF(ISNUMBER(A22),SUM(A$18:A22),"")
=IF(ISNUMBER(A23),SUM(A$18:A23),"")
=IF(ISNUMBER(A24),SUM(A$18:A24),"")
=IF(ISNUMBER(A25),SUM(A$18:A25),"")
=IF(ISNUMBER(A26),SUM(A$18:A26),"")

JL

That worked great too JumpingCrab. Thanks also to you for your creative solution.
It's amazing to me how many ways there are to accomplish the same thing so differently with Excel.