Welcome to the MacNN Forums.

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

You are here: MacNN Forums > Software - Troubleshooting and Discussion > Applications > Excel 2011 spreadsheet issue - formulas

Excel 2011 spreadsheet issue - formulas
Thread Tools
hksunshine
Fresh-Faced Recruit
Join Date: Apr 2005
Status: Offline
Reply With Quote
Jul 3, 2019, 09:59 AM
 
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.
     
andi*pandi
Moderator
Join Date: Jun 2000
Location: inside 128, north of 90
Status: Offline
Reply With Quote
Jul 3, 2019, 10:06 AM
 
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?
     
reader50
Administrator
Join Date: Jun 2000
Location: California
Status: Offline
Reply With Quote
Jul 3, 2019, 11:30 AM
 
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.
     
turtle777
Clinically Insane
Join Date: Jun 2001
Location: planning a comeback !
Status: Offline
Reply With Quote
Jul 3, 2019, 08:32 PM
 
Originally Posted by hksunshine View Post
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:

Code:
=G45-E46+F46
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
     
   
Thread Tools
 
Forum Links
Forum Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Top
Privacy Policy
All times are GMT -4. The time now is 07:53 PM.
All contents of these forums © 1995-2017 MacNN. All rights reserved.
Branding + Design: www.gesamtbild.com
vBulletin v.3.8.8 © 2000-2017, Jelsoft Enterprises Ltd.,