Running total on a ledger

Chris Grinstead

Hello,

I have a general ledger that I'm trying to calculate a running total on. I'm trying to get a column formula in my balance column to continue from the Total Starting Balance in my Sheet summary all the way down the list by the following formula =[Balance]@row + [Debit (+)]@row - [Credit (-)]@row

Is there a way to do this?

https://publish.smartsheet.com/08a37f677b3042679af3978549954387

Antonio Figueroa

After typing in the formula right click the cell with the formula and click on Convert to Column Formula

Chris Grinstead

When I do that, it's trying to take a value that's not there from the top row. It won't allow that formula to be a Column formula because of that. Here is the error message I get.

Heather Duff

@Chris Grinstead, I have had the same problem. Column formulas don't do well with formulas that reference cells in previous rows - I suspect because row 1 doesn't have a "previous row" to reference. Does that make sense?

Chris Grinstead

Yes it does. I was just wondering if anyone has figured out a solution for that problem.

Thaddeus Christian

I used 2 columns on my sheet to do a similar task.

I wanted to get the last reading date entered by the form to know the last day my maintenance staff read the water meter. This allowed me to get a more accurate average for my reporting. I used column formulas for all.

1. "Reading Date" : Column Where I Have Dates entered through a web form. - Date Format
2. "Entry Number" : Column Formula I Used: =MATCH([Reading Date]@row, [Reading Date]:[Reading Date], 0) - Text/Number Format

Answer column - "Last Reading Date" : Index(column:column, [Entry Number]@row -1) - Date Format

Answer column 2 - [Reading Date]@row - [Last Reading Date]@row -Text/Number Format

Worth noting that the first row will give you an error but it does not stop the functions of the sheet for me.

Heather P

Were you able to find a solution for this issue? I'm trying to accomplish the same thing and struggling.

Paul Newcome

@Heather P Are you able to provide a screenshot for context?

Heather P

Sure! The Balance column is where I'd like to use a column formula, but can't because I need to the balance from the previous row. So I'm forced to manually input a formula like this anytime a new row is added:

=Balance1 - [Marketing Fund Deductions]@row + [Marketing Fund Additions]@row

Paul Newcome

@Heather P You are going to need to enter an auto-number column (called "Auto" in this example). Then you will need a text/number column (called "Row" in this example) that has this column formula:

=MATCH(Auto@row, Auto:Auto, 0)

Then in the Balance column you can use

=SUMIFS([Marketing Fund Additions]:[Marketing Fund Additions], Row:Row, @cell<= Row@row) - SUMIFS([Marketing Fund Deductions]:[Marketing Fund Deductions], Row:Row, @cell<= Row@row)

Heather P

This worked! Thank you so much!

Paul Newcome

@Heather P Happy to help. 👍️