Pt1420 Unit 6

Line 3: Which row from the table of highlighted data will the answer come from? In this example 2 rows in the table were highlighted. The first row is the list of Marks; the second row is the list of Grades. Therefore the answer will come from row 2. (REMEMBER – IGNORE THE ROW NUMBERS GIVEN IN THE WORKSHEET – ONLY LOOK AT THE TABLE OF GRADES TO FIND OUT WHICH ROW WILL GIVE YOU THE ANSWER)

[PIC REF SS.42] Line 4: Range_lookup – there is no exact match for any of the marks – all the marks fall within the ranges 40%-50%, 50%-60% or 60%-70% and so the closest match will have to be found. Therefore this should be TRUE.

[PIC REF SS.43] TRUE will look for the closest match but always below – John smith’s mark of 88% will be matched

However you may only want to add a series of numbers that match specific criteria. For example you want to add the Total Sales only for the Aberdeen Branch – Aberdeen is your criteria.

[PIC REF SS.46a] Click on the cell where you want your answer to appear. Click on fx and select the SUMIF function. The SUMIF Functions Arguments dialog box will open.

[PIC REF SS.46b] Line 1: Range –the area containing all possible matches for your criteria – in this case Aberdeen is found in the column headed Branch which is the range of cells B4:B18.

Line 2: Criteria – the word or number that must be matched – in this case it is the word Aberdeen.

[PIC REF SS.47] [PIC REF SS.48] [DF] When keying in text in a spreadsheet formula remember to use inverted comments “ “ around the word(s).

Line 3: Sum_range – the range containing the numbers that you want added together if the criteria has been met.

Click OK.

[PIC REF

[DF] It is sometimes possible to include the cell references of information included in the worksheet as part of the formula rather than keying in criteria separately. This means that you can fill formula down the column and the criteria will change automatically. However you must remember to make use of absolute cell references to ensure the ranges do not change when they are copied down.

[PIC REF SS.50] In this example the range containing the Branch names does not change (B4:B18) nor does the range containing Total Sales (C4:C18). Therefore both these ranges should be fixed by pressing the F4 key. Point and click on the first of the criteria, which is Aberdeen (cell A22), but leave this as a relative cell reference. When the formula to find the Total for each of the branches is filled down from A22 the Criteria line will change to cell A23 (Perth) then cell A24 (Glasgow), then cell A25 (Edinburgh) and finally cell A26 (Dundee) but neither range will change.

[C HEAD] COUNT, COUNTA and COUNTIF

[PIC REF SS.52] [PIC REF