Excel/Help with formula
QUESTION: Dear Aidan,
I want to calculate the sum of each subsequent 2 rows. What formula can I use?
Year Sex Pop size
A1 2001 M 100
A2 2001 F 110
A3 2002 M 120
A4 2002 F 125
Then I want automatically calculate population of both sexes in each year. I calculated sum (A1:A2) and want to automatically calculate sum (A3:A4)etc.
Thank you for your help in advance.
ANSWER: Depending on the size of the data, I can think of a number of approaches. The easiest would be to insert a pivot table which would enable you to summarise the data however you wanted.
The second easiest, would be to have no formula in D1, the formula =a1+a2 in D2. Select both of these cells and then drag the formulas down using the fill handle at the bottom right of the selection box - this would give you the formula on every second row. For two cells I wouldn't use the sum function as =a1+a2 is shorter and does the same thing!
---------- FOLLOW-UP ----------
QUESTION: Dear Aidan,
I used your way and it worked perfectly before. I had problem again. When I fill down it showed error (omits adjacent cell) and when I filled down the next rows are automatically changed. For ex:
2 16 31
B2=sum(A1:A2)If I select A1:B2 and fill down then A3 and A4 are changed to 17 and 18, respectively and B4 becomes 35. I tried ignoring error also unchecked error check but still failed. What should I do please help. Thank you very much."
The "omits adjacent cells" is a warning message that there may be a problem with the formula, but it isn't an error as such. It simply suggests that Excel thinks you may have made a mistake. As to the autofill, excel will apply logic to fill. If you fill a formula down (which is column B) then excel will repeat the formula, amending cell references where they are relative (so =a1+A2 would become =a3+a4 when moved two cells down). For numbers (which you have in column A) excel uses whatever pattern it thinks it can detect. In your case, the first two numbers are 15 and 16, so the progression is one number up for each cell. I THINK you have actually selected the wrong cells to autofill. You should (I think) only select b1:b2 and fill THOSE cells down, which would then just update the formula and not the value cells.
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here