Become a MacRumors Supporter for \$50/year with no ads, ability to filter front page stories, and private forums.

hksunshine

macrumors newbie
Original poster
Just stepped down as Commander and elected treasurer of local disabled veterans group.
Have Excel spreadsheet (Office for Mac 2011) on our Macbook Pro computer (OS10.14).
Last treasurer made no entry so we input the debits and credits (Column E, F) but some Column “G” Balances and final balance are wrong. We need to correct before audit.

Columns are: A Date, B Check #, C Description, D Reason, E Debit, F Credit, and G Balance

We want to have a Balance formula which repeats in column "G" row after row to row 1000 and beyond. How do we do that?
Here is formula from row 46 that gave a correct balance.
=IF(ISBLANK(A46),"",SUM(G45-E46+F46))

Basically we need previous row column G balance, minus debit and/or plus credit in this row to give new column “G” balance in this row.

We want to know how to make the formula supersede current formula or lack of one in every row of “Balance”, column "G". We could start that at row 2 for the entire document if that is easier.

Balance in some rows has the correct formula, while others have no formula, wrong formula, or numbers were just forced in.

Can someone tell us how to do this and keep it in simple steps for old Vietnam Veteran not familiar with Excel.

Last edited by a moderator:

CTHarrryH

macrumors 68030
Although you may get answers here I'd suggest a Microsoft support forum to ask this type of question.

Beachguy

hksunshine

macrumors newbie
Original poster
Although you may get answers here I'd suggest a Microsoft support forum to ask this type of question.
Thank you, I will do that.

Gator1pk

macrumors member
The formula checks to see if there is a value entered in A46. If A46 has empty (blank), then don’t follow formula and show G46 blank. If there is a value in A46, then show the calculation in the SUM formula.

The formula is wrong, it should be SUM(G45+E46-F46). Credits in column F cannot be entered as a negative number. Follow the formula I just wrote and accounts that have a natural debit balance, like Cash, will be shown as a positive number. And accounts with a natural credit balance will be shown as negative number. You need to copy that formula from cell G2 all the way down to G1000. That’s assuming you start inputing data in Cell B2, as row A is your header.

I have a Bachelor and a Masters in Accounting from the University of Florida, I am a CPA and have 25 years of accounting experience. Advanced Excel user.

jasnw

macrumors 65816
The easiest answer to his question (unless I'm missing something simple, not unheard of) is that he should simply copy one of the cells in column G that has the correct formula and paste it in all 1000+ cells in column G. Goes without saying that he should make a backup copy of the original before doing anything, just in case.

sub150

macrumors 6502
Next is the question about having a whole trial balance in excel

Beachguy
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.