How do I extend a spreadsheet and keep the balance going?

Question

Level 1

9 points

How do I extend a spreadsheet and keep the balance going?

Hi All,

I'm fairly new to Mac and numbers and am having a difficult time working with a fairly simple situation. I imported a spreadsheet from Excell; a fairly straightforward record of a checking account. I have a date column, a description column, 4 deposit columns (4 people are depositing in this account to cover our folks retirement home), one expenses column (the once a month outgoing checks) and a balance column. My problem is very simple, I've run out of room on the original spreadsheet and want to add additional rows but when I do so with the "Add Rows Above" button, the formula for the balance no longer works in the added cells.

I've been through the help section to no avail. This should be a piece of cake, but I can't find anything to tell me either how to add additional rows to the spreadsheet with the formula that is there extending onward, or how to add a balance formula for multiple rows.

Thanks!

MacBook Pro, Mac OS X (10.6.2)

Posted on Mar 29, 2010 8:50 AM

Reply

Answer 1

Ginridge Author

Level 1

9 points

Mar 29, 2010 9:23 AM in response to KOENIG Yvan

My apologies. I assumed a simple balance formula is a standard thing. Here's what's in the part of the spreadsheet that works: =SUM(I70,D71,E71,F71,G71,-H71)>

This is an ongoing balance formula that continues through the spreadsheet adding any deposits and subtracting any withdrawals giving a running balance of the account after each transaction. The specific row references change to be appropriate for each additional row (so for the balance on row 70, the formula would read =SUM(I69,D70,E70,F70,G70,-H70) etc.).

I have now run out of empty rows on the original spreadsheet and wish to add additional rows to extend the spreadsheet but when I add rows below, the formula does not automatically extend to the new rows. If I add rows in between rows with the formula in it, the added rows come up blank as well with no formula in them.

It seems to me that there should be a simple way to add additional rows and keep the formula intact on those new rows or there should an easy way to add an ongoing formula to a series of rows.

That's what I'm trying to find out about.

Thanks!

Reply

Answer 2

KOENIG Yvan

Level 8

41,825 points

Mar 29, 2010 9:09 AM in response to Ginridge

Posting the formulas here:
the one which behaves correctly and the one which doesn't
would be useful if you want help.

We aren't sooth sayers able to see what is in your computer.

Yvan KOENIG (VALLAURIS, France) lundi 29 mars 2010 18:08:48

Reply

Answer 3

Mar 29, 2010 9:56 AM in response to Ginridge

G,

If the Table has identical formulas in every row of the Body of the Table, they will replicate upon adding rows. If there is a break in the pattern, anywhere in the table, they will not replicate.

This is an easy rule to comply with since we can put any extraneous data or calculation in Header or Footer rows which don't affect what's going on in the body rows.

Jerry

Reply

Answer 4

Ginridge Author

Level 1

9 points

Mar 29, 2010 10:17 AM in response to Jerrold Green1

Thanks for the response. This spreadsheet (table?) has the ongoing balance formula in place from row 2, column I where the entries begin through row 71 column I where I now am running out of space. I also have formula's to total each of the individual columns D, E F, G and H at the bottom of the page, now on row 120 since I am trying to add the additional rows. I would assume, if I am understanding your posting correctly, that if I added in additional rows, the balance formula would extend and continue with the added rows, but clearly, this is not the case. I appreciate what you are saying about keeping the formulas continuous, but aren't I doing that now? In this specific case, can you see anything that I am doing wrong?

Are the formulas to total the rows (which do work in the new position by the way) interfering with the process? I tried removing the blank row at the top (row 1) to see if that would solve the problem, but it didn't; I just got a red diamond in each of the balance entries throughout the spreadsheet indicting the formula contained an incorrect reference.

Assuming there is some issue I can't detect, isn't there a simple way to add an ongoing formula to the newly added rows?

Thanks!

Chris

Reply

Answer 5

Barry

Level 9

60,035 points

Mar 29, 2010 10:44 AM in response to Ginridge

Ginridge wrote:

I have now run out of empty rows on the original spreadsheet and wish to add additional rows to extend the spreadsheet but when I add rows below, the formula does not automatically extend to the new rows. If I add rows in between rows with the formula in it, the added rows come up blank as well with no formula in them.

It seems to me that there should be a simple way to add additional rows and keep the formula intact on those new rows or there should an easy way to add an ongoing formula to a series of rows.

Hi Chris,

When you add new rows by dragging the Row Handle, formulas will be automatically added to the new rows in columns where EVERY row (withe the exception of header or footer cells) contains the SAME formula. SAME in this case includes the changes in cell reference noted in your example.

If there are any cells in a column that are empty or that contain a different formula, Numbers will not copy a formula into added rows. But filling the formula down into adjacent cells is a fairly easy task:

From "Autofilling Table Cells" in page 76 of the Numbers '09 User Guide:

To paste the content and fill of a cell into adjacent cells, select the cell and then drag the Fill handle (a small circle in its lower-right corner) over the cells into which you want to paste.
Any data, cell format, formula, or fill associated with the selected cell is pasted, but comments aren’t pasted. If any target cell contains data, autofilling overwrites that data with the value you’re repeating.
(...more)

If you haven't yet downloaded and taken a look at the Numbers '09 User Guide and the iWork Formula and Functions User Guide, I'd strongly suggest doing so. You'll find links for both in Numbers's Help menu. User Guides for Pages and Keynote are also available through the Help menus in this iWork applications.

Regards,
Barry

PS: Here's a minor revision to your balance formula that keeps the current balance cell (and those below it) blank until data is entered in that row:

=IF(COUNT(D70:H70)>0,SUM(I69,D70,E70,F70,G70,-H70),"")

Reply

Answer 6

Barry

Level 9

60,035 points

Mar 29, 2010 10:55 AM in response to Ginridge

Ginridge wrote:
Thanks for the response. This spreadsheet (table?) has the ongoing balance formula in place from row 2, column I where the entries begin through row 71 column I where I now am running out of space. I also have formula's to total each of the individual columns D, E F, G and H at the bottom of the page, now on row 120 since I am trying to add the additional rows. I would assume, if I am understanding your posting correctly, that if I added in additional rows, the balance formula would extend and continue with the added rows, but clearly, this is not the case. I appreciate what you are saying about keeping the formulas continuous, but aren't I doing that now? In this specific case, can you see anything that I am doing wrong?

Is Row 1 a Header Row? If it's a 'regular' row, then the formula or value it contains is included in the cells that must contain the 'same' formula.

Is the bottom row a Footer Row? If not, then it too must contain the 'same' formula in column I. If it is, and your SUM() formulas are in this row, things should be working.

Regards,
Barry

Reply

Answer 7

Ginridge Author

Level 1

9 points

Mar 29, 2010 11:12 AM in response to Barry

Thanks very much, Barry! I was able to past that formula in and drag it down through the new rows.
That's exactly what I was looking for. I have also downloaded the user's guide and will refer to it rather than the help box in the future.

I don't know why it didn't automatically do that, but since this was imported as an Excel file, then converted to numbers, perhaps that caused the glitch.

Best regards,

Chris

Reply

Answer 8

KOENIG Yvan

Level 8

41,825 points

Mar 29, 2010 11:35 AM in response to Ginridge

I built from scratch a table with the described columns and the given formula.
It behaves flawlessly.
My guess is that maybe the row 1 of your table is not a true header one.
With such a table (and I would not be surprised that it's the real case for a table imported from XL)
the rule:
the formula must be the same in every rows {which in fact means every rows minus header one(s)}
can't be applied and so, inserting a new row will not insert the formula automatically.

Yvan KOENIG (VALLAURIS, France) lundi 29 mars 2010 20:35:12

Reply

Answer 9

Mar 29, 2010 12:46 PM in response to Ginridge

Ginridge wrote:
Thanks for the response. This spreadsheet (table?)

While I was out shopping, I see you have received ample help. Some notes then on the nomenclature.
o A Numbers Document will have one or more Sheets. No Sheet, no content.
o Each Sheet will have a Canvas.
o The Canvas may be broken up into Pages if in Print View.
o Each Canvas may have Tables, Text Boxes, Charts, Shapes, Pictures and other objects.
o Each Page may have Headers and Footers, if enabled.
The Numbers User Guide will help with this. The Formulas and Functions User Guide is applicable to all three iWork applications where Tables are involved.

Jerry

Reply

Answer 10

KOENIG Yvan

Level 8

41,825 points

Mar 29, 2010 1:02 PM in response to Jerrold Green1

Jerrold Green1 wrote:
o Each Page may have Headers and Footers, if enabled.

As far as I know, we can't disable header/footer in Numbers.

Setting 0 in both dedicated fields just
move the header to the top of the page
move the footer to the bottom of the page.

I already reported this oddity to Apple.
I did it through different channels with the same nil result.

Yvan KOENIG (VALLAURIS, France) lundi 29 mars 2010 22:01:46

Reply

Answer 11

Mar 29, 2010 1:28 PM in response to KOENIG Yvan

Yvan,

You're absolutely correct. I must have been thinking of Pages Page Layout mode. I've never put any content in a Numbers Header or Footer, so I guess I shouldn't be going on about it. To me they are in the way.

Sorry you had no success raising the issue to Apple.

Jerry

Reply

Answer 12

KOENIG Yvan

Level 8

41,825 points

Mar 29, 2010 2:11 PM in response to Jerrold Green1

I thaught that in the Numbers team they don't understand that the numerical values which we enter aren't the height of header/footer but the offsets of header from top of page and the offset of footer fom bottom, of page.

But the names used in some resources file for these values are clearly embedding the word offset.

I will make a new attempt because in France we say :
"jamais deux sans trois" and I checked that I sent the request twice (directly from me to bugs hunters.
Here I don"t count attemps with an intermediate.

Yvan KOENIG (VALLAURIS, France) lundi 29 mars 2010 23:11:22

Reply

Answer 13

Mar 29, 2010 2:44 PM in response to KOENIG Yvan

Here we often say: "The third time is a charm."

Reply