|
|
Excel 2011 spreadsheet issue - formulas
|
|
|
|
Fresh-Faced Recruit
Join Date: Apr 2005
Status:
Offline
|
|
Just elected treasurer of local disabled veterans group. Last treasurer didn’t keep good records.
Have Excel spreadsheet (Office for Mac 2011) on our Macbook Pro computer (OS10.14).
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 the Balance formula repeat in column "G" thru 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 it is 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 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.
Thank you.
|
|
|
|
|
|
|
|
|
Moderator
Join Date: Jun 2000
Location: inside 128, north of 90
Status:
Offline
|
|
I am not great with excel, hopefully someone will be along. In my experience the formulas are fussy. We have an expense form at work that never adds things up correctly. Can you inspect the formulas?
|
|
|
|
|
|
|
|
|
Administrator
Join Date: Jun 2000
Location: California
Status:
Offline
|
|
Last treasurer didn't understand spreadsheets well. The missing formulas with hard-typed values are a giveaway.
It sounds like you want to duplicate the formulas down the rows. Fill function and even copy-paste does relative copies by default, which is what you want.
To fix it by hand, select a cell with correct formula. Hit Copy. Select one with missing formula, hit Paste. It should paste the formula (not the value) with the cell references adjusted for the new location.
Fill-Downwards can do entire column(s), assuming Row2 has correct formulas. Select the zone you want filled, then issue the fill-down command. I don't have MS Office to check the command to use. In LibreOffice, the command is in the menus: Sheet -> Fill -> Down. Shortcut is Command-D for a down-fill.
|
|
|
|
|
|
|
|
|
Clinically Insane
Join Date: Jun 2001
Location: planning a comeback !
Status:
Offline
|
|
Originally Posted by hksunshine
How do we do that? Here is formula from row 46 that gave a correct balance. =IF(ISBLANK(A46),"",SUM(G45-E46+F46))
Ok, so basically, it sounds like all you need is this:
That's it. The rest should not be needed.
I'm not quite sure if this condition has any real purpose.
Code:
=IF(ISBLANK(A46),"",
In the spreadsheet as you describe it, it would stop calculating if the DATE field is blank.
What you don't tell us is what data exists in columns E, F and G, IF the DATE field is blank.
If these cells are blank as well, the formula above would interpret an empty (null) cell as 0.
Everything would work as expected.
HOWEVER, if the rows with a DATE = blank contain a SUBTOTAL, and you want to ignore this subtotal, and start calculating fresh in the next row, then the original formula is basically ok. It can be abbreviated slightly to
Code:
=IF(ISBLANK(A46),"",G45-E46+F46)
-t
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Forum Rules
|
|
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
|
HTML code is Off
|
|
|
|
|
|
|
|
|
|
|
|