{"id":300,"date":"2011-10-05T09:45:09","date_gmt":"2011-10-05T09:45:09","guid":{"rendered":"http:\/\/www.microsofttraining.net\/b\/exceltraining\/?p=300"},"modified":"2023-12-30T23:13:57","modified_gmt":"2023-12-30T23:13:57","slug":"using-auto-sum","status":"publish","type":"post","link":"https:\/\/www.stl-training.co.uk\/b\/using-auto-sum\/","title":{"rendered":"Using Auto Sum"},"content":{"rendered":"

Let\u2019s look at an example using a formula that provides a total amount.<\/p>\n

\"excel-formulas-summing\"<\/a><\/p>\n

In the total row, we want to display the sum of all household expenditure. Make sure that the cell you want to contain the total is active and then click Home > Editing > AutoSum. Because the active cell is positioned directly below a column of numbers, Excel 2010 guesses that you want to sum them and places a selection around them (see A below).<\/p>\n

\"excel-formulas-autosum\"<\/a><\/p>\n

B is called the Formula Bar<\/strong> and it displays the content of the active cell, which in this case is a formula. C is the active cell and contains the formula. Let\u2019s look at the structure of a formula.<\/p>\n

You can see that the formula starts with a \u201c=\u201d.\u00a0 All formulas must start with \u201c=\u201d. The next part is SUM, which you can guess represents the SUM function. The contents of the brackets are called arguments and they define the range of cells that we are summing \u2013 in this case cells B2 to B7. This range is defined as B2:B7. Autosum is a quick and easy formula to use that automatically identifies its input variables. Let\u2019s now look at a formula we can set up manually.<\/p>\n

Defining Your Own Formula In Excel<\/h2>\n

Suppose we have the following example that details price, VAT and total price.<\/p>\n

\"excel-define-your-own-formula\"<\/a><\/p>\n

As you can see, we start with a price before VAT and we need to calculate first the VAT and then the total price. Let\u2019s calculat the VAT first, based on a VAT rate of 17.5%. The formula therefore is VAT = 17.5% x Price. Make cell B2 active and in it type
\n=17.5%*A2<\/code>
\nExcel knows how to handle percentages so you don\u2019t need to convert 17.5% to 0.175 for the calculation. When you press enter, Excel calculates the VAT to be 43.75. We have dealt with the first VAT amount, but what about the rest? There is a quick and easy way to apply the formula you just created to other cells. With cell B2 active, position the cursor over the bottom right corner so that the cursor becomes a \u2018+\u2019. When it does, drag downwards to select all cells that should use the formula (down to cell B6).<\/p>\n

\"excel-apply-formula-to-other-cells\"<\/a><\/p>\n

When you release the mouse, Excel applies the VAT formula to the selected cells and calculates their values. That\u2019s the VAT for all rows take care of. On to the total price.<\/p>\n

Total price = price + VAT<\/p>\n

We can define that as a formula by typing into C2 the following
\n
\n=A2+B2
\n<\/code>
\nWhen you press Enter Excel calculates the sum of A2 and B2 to give 293.75. And we\u2019ve already seen how we can to apply that formula to the remaining cells. The finished spreadsheet should look like this.<\/p>\n

\"excel-formulas\"<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

Let\u2019s look at an example using a formula that provides a total amount. In the total row, we want to display the sum of all household expenditure. Make sure that the cell you want to contain the total is active and then click Home > Editing > AutoSum. Because the active cell is positioned directly […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[4],"tags":[59,173,433],"class_list":["post-300","post","type-post","status-publish","format-standard","hentry","category-excel-training","tag-auto-sum","tag-excel-2010","tag-pr"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/300","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/comments?post=300"}],"version-history":[{"count":1,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/300\/revisions"}],"predecessor-version":[{"id":6783,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/300\/revisions\/6783"}],"wp:attachment":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/media?parent=300"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/categories?post=300"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/tags?post=300"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}