Lesson Objectives
At the end of this lesson the reader should be;
- Able to create understand how to create formulas
- Able to use the Sum function
- Familiar with Cell references and the various types of cell referencing
- Able to understand excel arguments and syntax in formulas
- Able to use Excel Auto functions
- Able to do Excel Auto calculate
- Give name to a cell or Ranges as an alternative to Cell References
- Able to understand how excel performs its calculations
Creating a formula in Excel using the “SUM function”
Recall from our previous lesson, we discussed that to make a valid Excel formula, there are basically two rules to follow;
- The formula must start with an equal to sign (=), Plus sign (+), minus sign (-) or a Macintosh ({}) used for an array type of formulas.
- The right use of brackets and a comma to separate arguments from each other is very important
How to apply the SUM function
Type the number 1 to 10 in cell A1:J1, then on cell K1, type (=sum) then type an Open bracket “(“ , and type (A1:J1) then type a close bracket “)”. Your formula on cell K1 should look like this “ =sum(A1:J1)”Also try this on Cell K2 – Type (=sum) then type an Open bracket “(“ , and type (A1) then type a comma “,” type (C1), type a comma “,”, then type (J1) and then close the bracket “)”. Your formula should look like this “=Sum(A1,C1,J1)”
Now let’s analyze the two formulas
Sum (A1:J1) – Contains a single argument and excel will always add up anything withing the range A1 through J1
Sum(A1,C1,J1) – Contains 3 arguments and excel will only add up the specified cells (A1, C1 and J1)
Note – Some functions requires one arguments while others can require more e.g. a “VLOOKUP()” function will take only four arguments.
Auto Functions /Insert Functions(fx)
The Excel auto functions/insert functions makes the use of Excel functions easier and you don’t have to worry about forgetting any formula. Just open up the fx category box as I explained earlier and you can read the uses of each predefined functionsLet’s try this with an Excel AutoSum Function. On a clean Worksheet insert any numbers In the Range D5:D10 and F5:J5, Select Cell D15 and Click the AutoSum Icon on the Standard Toolbar or select the Sum function from the function category box.
You should get the formula =SUM(D5:D14)
Excel AutoCalculate
There might be times when you wish to find out the sum of a range of numbers quickly without having to add a formula to the Worksheet. The AutoCalculate feature of Excel would make this very easy and possible for you. To make use of this function, select a range of cells containing the numbers you want to sum and look in the Status bar for the result. If the Status bar is not visible go to View - Status bar. If the range of cells you wish to sum are not on same rows hold down the Ctrl key while selecting the ranges. You change to other functions like count and average if you are using the older version of Excel, otherwise the newer version would automatically display all available result for you.Other commonly used functions in excels are the; AVERAGE, COUNT, COUNTBLANK, COUNTA, COUNTIF, MAX, MIN, SUMIF, AVERAGEIF,
AVERAGE
The AVERAGE function is used to return the average of the arguments supplied.Syntax
AVERAGE(number1,number2, . . . )
The AVERAGE function can take up to 30 arguments.
The arguments supplied must be numeric or references to numeric values. Text and/or references to text are ignored. It is important to note that cells containing zeros are NOT ignored. This can give you unexpected results if you are not aware of it.
=AVERAGE(A1:A3) would equal 10 if A1:A3 contained 15, 5, 10 respectively
COUNT
The COUNT function is used to count numbers or references to numbers in a range.Syntax
COUNT(value1,value2, . . . )
The COUNT function takes up to 30 arguments and each argument can be a variety of data types, but only numbers are counted.
If the range reference supplied contains valid dates these will also be counted.
=COUNT(A1:A5) would equal 3 if cells A1:A5 contained Boy, Man, house, 8, dog
COUNTA
The COUNTA function is used to count non-empty cells.Syntax
COUNTA(value1,value2, . . . )
The COUNTA function takes up to 30 arguments and each argument should be a reference to a range. Cells within the range can be a variety of data types, but only non-empty cells are counted.
=COUNTA(A1:A5) would equal 4 if cells A1:A5 contained “ “, Man, house, Cat, dog.
Note A1 is empty and so is not counted while all other cells are.
COUNTBLANK
The COUNTBLANK function is used to count empty cells. It is the opposite of the COUNTA functionSyntax
COUNTBLANK(range)
The COUNTBLANK function takes 1 argument and this argument should be a reference to a range. Cells within the range can be a variety of data types, but only empty cells are counted
=COUNTBLANK(A1:A5) would equal 1 if cells A1:A5 contained “ “, Man, house, Cat, dog.
In other words A1 is empty and so is counted while all other cells are not.
COUNTIF
The COUNTIF function is used to count cells within a range that meet a specified criterion.Syntax
COUNTIF(range,criteria)
The COUNTIF function takes two (2) arguments. The range argument is a reference to a range of cells, while the criteria argument is the criterion that should be met by the cells within range before they are counted. The criteria specified can be in the form of a number, text or an expression.
Number criteria
=COUNTIF(A1:A5,20) would equal 1 if cells A1:A5 contained 2000, 10000, 20, 0,1
In other words A3 is the only cell that meets the criteria of 20
Text criteria
=COUNTIF(A1:A5,”Lami”)
would equal 2 if cells A1:A5 contained Bidemi, Ife, Lami, Femi, Lami
In other words A3 and A5 are the only cell that meets the criteria of “dog”
Expression criteria
=COUNTIF(A1:A5,”<20”) would equal 3 if cells A1:A5 contained 15, 30, 20, 0, 1.
In other words A1, A4 and A5 are the only cells that meets the criteria of “<20”.
MAX
The MAX function is used to return the largest number from a set of values.Syntax
MAX(number1,number2,. . . )
The MAX function takes up to 30 arguments and will ignore text.
=MAX(A1:A5) would equal 3 if cells A1:A5 contained 1, 2, 0, 3, -10
MIN
The MIN function is used to return the smallest number from a set of values.Syntax
MIN(number1,number2,. . . )
The MIN function takes up to 30 arguments and will ignore text.
=MIN(A1:A5) would equal -6 if cells A1:A5 contained 9, 8, -6, 10, 1.
SUMIF
The SUMIF function is used to return the sum value from a specified range that meets a criterion. This selection of the range must start from the range that contains the Criteria and covers the Sum_range where the result to sum is always startSyntax
SUMIF(range,criteria,sum_range)
The SUMIF takes 3 arguments. The range is the range of cells to check the specified criteria from. Criteria is the Identity to look for in the range, the criteria specified can be in the form of a number, text or an expression. The sum_range is the range of cells to sum but only if the corresponding cells in the range meet the specified criteria. If sum_range is omitted then the cells within the range are summed up.
=SUMIF(A1:A5,10) would equal 30 if cells A1:A5 contained 10, 8, 5, 10, 10 (cells A1, A4 and A5 would be summed as they meet the criteria and NO sum_range was supplied).
=SUMIF(A1:A5,5,B1:B5) would equal 2 if cells A1:A5 contained 5, 4, 1, 9, 5 and cells B1:B5 contained 1,1,3,8,1.(cells B1 and B5 would be summed as the corresponding cells in A1:A5 have a value of 5).
=SUMIF(A1:A5,”Boy”,B1:B5) would equal 15 if cells A1:A5 contained Boy, Boy, Boy, Girl, Girl and cells B1:B5 contained 5,5,5,8,11
( cells B1,B2 and B3 would be summed as the corresponding cells in A1:A5 contain the text “Boy” ).
=SUMIF(A1:A5,”>5”) would equal 35 if cells A1:A5 contained 10, 15, 0, 10, 5 and In other words cells A1, A2 and A4 would be summed as they meet the criteria of >5.
Customize/Label Ranges as an alternative to Cell References
In Excel we can give name to ranges for easy formula write-ups. For example we could name range A1: A7 Periods and name range B1:B7 Sales. If we are to use a SUMIF Function we would be referencing to range (B1:B7) as Sales and (A1:A7) Periods. Which makes the formula easy to comprehend and read. There are however certain rules one have to consider while naming a cell reference. These includes;
1. The first character of the name must be a character or an underscore “_”
2. Names cannot be the same as a cell reference e.g. XD100 or R1C1.
3. Spaces are not allowed, an underscore character may be used to separate words
4. A name can contain up to 255 Characters.
How to name ranges
Step 1.This is the simplest way of naming a cell range in an Excel and very easy to use.
1. Type The Word Amounts In Cell B1 And Names In Cell C1
2. Type 5, 20, 40, 50, 100, 200 in Cell B2:B7 Respectively.
3. Type The Names; Femi, Nehita, Blessing, Jecky, Femi, Oye In Cells C2:C7 Respectively
4. Now select the Range B2:B7
5. Locate the “name box” in the already opened workbook by the top left hand-side corner at the back of the “fx” button. Then type the name you want to give to the cell reference already selected in step 4 above.
Step 2.
1. Type The Word Amounts In Cell B1 And Names In Cell C1.
2. Type 5, 20, 40, 50, 100, 200 in Cell B2:B7 Respectively.
3. Type The Names; Femi, Nehita, Blessing, Jecky, Ife, Oye In Cells C2:C7 Respectively
4. Now Select The Range B1:B7 go to Insert – Name - Create. This Is The Create Names Dialog Box And Is Used To Create Names Based On The Current Region Row And/Or Column Headings. In This Case We Only Have Column Headings So Ensure That Only The "Top Row" Check Box Is Checked. What We Have Done By Doing This Is Told Excel That We Wish To Name The Range B2:B7 Amounts And The Range C2:C7 Names
Test
Perform step 1 in a new Excel workbook.
1. Type The Word Amounts In Cell C1 And Names In Cell B1
2. Type 5, 20, 40, 50, 100, 200 in Cell C2:C7 Respectively.
3. Type The Names; Femi, Nehita, Blessing, Jecky, Femi, Oye In Cells B2:B7 Respectively
4. Now select the Range C2:C7
5. Locate the “name box” in the already opened workbook by the top left hand-side corner at the back of the “fx” button. Then type the name you want to give to the cell reference already selected in step 4 above, do similar for range B2 : B7
In Cell E7, use a SUMIF function to find the Amount spent by Femi
Note: Excel formulas only read strings therefore whenever it is required to use text in our formula, we first need to convert the text in a string format. to do this you will just append an apostrophe at the front of the text and immediately after the last character of the text.
So, putting Femi in our expression would become 'Femi'
Joseph will become 'Joseph'
Boy will become 'Boy'
Congratulations! you have now successfully completed this lesson
Post a Comment