the formula D2 = A2 + B2 + C2 and copying the formula to all C3. write the formula on E3 cell?
Answers
A formula to sum the Food budget is =B2+C2+D2
A formula to sum the Rent budget is =B3+C3+D3
A formula to sum the Misc budget is =B4+C4+D4
Rather than typing in three formulas that are all essentially the same, we only need to type the first formula and we can get Excel to copy the formula into the other two cells.
Enter the formula =B2+C2+D2 in cell E2:
Press the Enter key and Excel will evaluate the formula for you:
Move the cursor back up to cell E2:
Position the mouse cursor in the lower right corner of cell E2 and the cursor will change into a fill handle (the little black plus sign – you need to be careful – the region where the cursor changes to the fill handle isn't very large):
Click and drag mouse down to the next three cells:
If you look at the formulas in cells E3, E4, and E5, you will see this:
When you copied the formula from E2 to E3 and E4, Excel automatically adjusted the terms in the formula. As you moved from row 2 to row 3, it replaced all of the 2's in the formula with 3's. And when you moved to row 4, it replaced the 2's with 4's. So each formula does exactly what it is supposed to do—it adds the three numbers to its left.
Now let's add totals to the bottom of the Totals column:
Click on cell E5.
On the Home tab, in the Editing group, click on the Autosum button:
.
You should see the following:
Excel is telling you that it will sum the cells E2 through E4. Sum is the name of a built-in function. The text in the parentheses (E2:E4) is called an argument to the function. An argument is the data that must be made available to the function so the function can compute an answer. The cells from E2 through E4 are called a range, and ranges are indicated in Excel by listing one corner of the range (usually the upper-left corner), followed by a colon, followed by the opposite corner. Excel gives you a visual clue by drawing a dancing line around the cells that it is going to sum. Always verify that the cells that are inside of the dancing line are actually the ones that you want to sum. Excel will usually guess the correct range, but not always (see the Average example below). Since Excel is guessing correctly this time, press the Enter key.
This formula can be copied to the previous cells in the row (B5:D5). Click on E5, get the fill handle, and drag to the left:
Excel will copy the formulas:
Now let's calculate our monthly averages.
Click in cell F2.
On the Home tab, in the Editing group (far right of ribbon), click on the Autosum button's down-arrow:
Click on Average.
Excel assumes that you want the average of all of the numbers immediately to the left. But Excel is assuming incorrectly!
You do not want to include the total from column E in your average. You can fix this by dragging the mouse over the correct cells: B2:D2.
Press Enter and use the fill handle to copy the formula down to the next three cells:
Now let's calculate the percent of our budget that is going towards food, rent, and misc. Click on G2 and enter the formula =F2/F5.
Press the Enter key to have Excel accept the formula:
Format the number in G2 as a percent by clicking on G2. Then, on the Home tab, in the Number group, click on the percent button.
Excel will display the number as a percent
Since we want to calculate the percentages for rent and misc, we need to copy the formula to cells G3, G4, and G5. Do the same thing that you did in column E. Click on the cell you want to copy (G2). Position the cursor in the lower right corner of the cell, and when the fill handle appears, drag the formula down to the next three cells.
This time we get an error! The #DIV/0! means that we are trying to divide by 0. To see what the problem is, look at the formulas that Excel copied for us:
So you have to know two things when you create a formula:
(1) Are you going to copy it?
(2) If so, are there any terms that you do not want changed when you copy the formula
Yes that answer is correct