Excel Question

[AMC]

Some of you have to be excel experts. I want to create a spreadsheet that will automatically add the totals in a column, even if I add a new row. Like so:

Row1: data data data data 100 data data data
Row2: data data data data 199 data data data

it's easy enough to create Row3 and have cell 3x5 be =1x5+2x5. But is there a way to make it so that if I want to add a new Row3, bump my totals down to Row4, and I don't have to manually say that cell 4x5 =1x5+2x5+3x5?

Thanks

 

[jen]

Re: Excel Question

Some of you have to be excel experts. I want to create a spreadsheet that will automatically add the totals in a column, even if I add a new row. Like so:

Row1: data data data data 100 data data data
Row2: data data data data 199 data data data

it's easy enough to create Row3 and have cell 3x5 be =1x5+2x5. But is there a way to make it so that if I want to add a new Row3, bump my totals down to Row4, and I don't have to manually say that cell 4x5 =1x5+2x5+3x5?

Thanks

you can do =sum(e1:e2) on row3.. then if you insert a new row 3, it will adjust it to include in the total. In office 2007.

 

[SteveF]

Re: Excel Question

i dont follow completely.. but if you use the sum(A1:A10) function, everytime you add a row in that range it will include it in the total.

edit

you can do =sum(e1:e2) on row3.. then if you insert a new row 3, it will adjust it to include in the total. In office 2007.

yah what she said

 

[pirate]

Re: Excel Question

you can do =sum(e1:e2) on row3.. then if you insert a new row 3, it will adjust it to include in the total. In office 2007.

I don't know if that is a setting/option but that isn't how 2007 works for me. If you add a new row 3, moving the sum(e1:e2) formula to row 4, then the formula stays sum(e1:e2).

We've just grown accustomed to leaving a blank line and then putting the formula in row 4 to start. So

a1 Data
a2 Data
a3 blank
a4 Formula sum(a1:a3)


Then if you add a line below row 2 it automatically changes the formula to account for the new line.

 

[JF_Gophers]

Re: Excel Question

Your best bet to solve this is to create a simple VB function in Excel that you can call in your spreadsheet.

The function could iterate through the column you are totalling until it found the last entry. Then it would know how many rows to add together for the total.

 

[jen]

Re: Excel Question

I don't know if that is a setting/option but that isn't how 2007 works for me. If you add a new row 3, moving the sum(e1:e2) formula to row 4, then the formula stays sum(e1:e2).

We've just grown accustomed to leaving a blank line and then putting the formula in row 4 to start. So

a1 Data
a2 Data
a3 blank
a4 Formula sum(a1:a3)


Then if you add a line below row 2 it automatically changes the formula to account for the new line.

How do you insert the new line? Using "shift cells down" or "entire row"?

Using - right click - insert - entire row - it works.

Then I tried using right click - insert - shift cells down - and it did NOT work.

 

[AMC]

Re: Excel Question

How do you insert the new line? Using "shift cells down" or "entire row"?

Using - right click - insert - entire row - it works.

Then I tried using right click - insert - shift cells down - and it did NOT work.

This worked. Thank you.

 

[St. Clown]

Re: Excel Question

Your best bet to solve this is to create a simple VB function in Excel that you can call in your spreadsheet.

The function could iterate through the column you are totalling until it found the last entry. Then it would know how many rows to add together for the total.

Re-read the question that was asked? Do you think this is a user who knows how to write VB scripts or use the macro writer?

If in doubt about whether the formula will adjust when inserting a row at the end, insert the row in the middle instead. You're guaranteed that the formula will adjust to your needs.

Also, simply use the Autosum function in the toolbar - the sigma button that looks like this: ∑. Double-click it when your active cell is at the end of a list of numbers and the summation formula will automatically populate that active cell.

 

[JF_Gophers]

Re: Excel Question

i found another great solution thats also really simple.

In the cell you want to be your total create a variable range. For example if your total was in A11 and your data column was A.

- In Excel 2007 you can just right-click the cell and select "Name a range".
- Give it a name like "prevCell".
- In the Refers To field put: =A10 (or =Sheet1!A10) without the $ signs.
- Click "OK"
- Now in A11 put your SUM formula: =SUM(A1revCell)

No matter where you add a row it will keep adding all the of the A cells above your total. Even if you insert/shift a cell and not a row.

 

[AMC]

Re: Excel Question

i found another great solution thats also really simple.

In the cell you want to be your total create a variable range. For example if your total was in A11 and your data column was A.

- In Excel 2007 you can just right-click the cell and select "Name a range".
- Give it a name like "prevCell".
- In the Refers To field put: =A10 (or =Sheet1!A10) without the $ signs.
- Click "OK"
- Now in A11 put your SUM formula: =SUM(A1revCell)

No matter where you add a row it will keep adding all the of the A cells above your total. Even if you insert/shift a cell and not a row.

Hmm.... will this work if I add cells right above my bottom row? Like if I have rows A-G, and then add a new G, so my new totals row is H. My "G" is now blank. So would it catch anything in G if my "prevCell" is =F10 now?

Does that make sense?

jen's solution is kind of working, but not perfectly. It seems like this would be the kind of thing that you would be able to do easily, I can't be the only person that has ever wanted to do this.

 

[JF_Gophers]

Re: Excel Question

Hmm.... will this work if I add cells right above my bottom row? Like if I have rows A-G, and then add a new G, so my new totals row is H. My "G" is now blank. So would it catch anything in G if my "prevCell" is =F10 now?

Does that make sense?

jen's solution is kind of working, but not perfectly. It seems like this would be the kind of thing that you would be able to do easily, I can't be the only person that has ever wanted to do this.

It will work no matter where you add the row or column as long as its before the total field.

If your total is moving to the right then the prevCell would be to the "left" of the total (i.e., adding columns).

If your total is moving down (i.e., adding rows), then the prevCell would be "above" the total.

The reason being that Excel is automatically adjusting your formula for you as you add rows/columns.
If you start with =A10 and add a row, that cell will now say =A11. This is why you are not using the $ sign, as that would lock the cell reference to $A$10.

 

[AMC]

Re: Excel Question

I like the sound of it, but I have Excel 2008 Mac edition, when I right click on a cell I don't get a Name a Range option.

I tried to search for it in Excel's help files but found nothing.

 

[JF_Gophers]

Re: Excel Question

I like the sound of it, but I have Excel 2008 Mac edition, when I right click on a cell I don't get a Name a Range option.

I tried to search for it in Excel's help files but found nothing.

In Help you want to look for "Define Name" or "Name Manager"

Its probably under Insert --> Name --> Define, or you can try the key code [Command]+[F3]

 

[AMC]

Re: Excel Question

In Help you want to look for "Define Name" or "Name Manager"

Its probably under Insert --> Name --> Define, or you can try the key code [Command]+[F3]

Working perfectly, thank you so much.

 

[CLS]

Re: Excel Question

What I typically do in a case like that is to put the sum in the top row. So, for example, in Excel, in cell A1, I enter

=sum(A2:An)

where "n" is the largest number I will possibly use. If it's going to me a very large number (e.g. n=2000), I split the window, so row 1 and rows 1975 - 2000 are in the window.

 

[St. Clown]

Re: Excel Question

What I typically do in a case like that is to put the sum in the top row. So, for example, in Excel, in cell A1, I enter

=sum(A2:An)

where "n" is the largest number I will possibly use. If it's going to me a very large number (e.g. n=2000), I split the window, so row 1 and rows 1975 - 2000 are in the window.

=sum(A:A) will sum the entire A column, and =sum(1:1) will sum all of row 1. Just make sure your formula is written in another column or row, otherwise you'll have created a circular reference.

Another way to name a cell is highlight the portion of the screen that tells you current active cell - it's just to the left of the formula bar - and assign the name by typing it in there.