Documents

Spreadsheet Count Tutorial 2015

Description
Description:
Categories
Published
of 3
9
Categories
Published
All materials on our website are shared by users. If you have any questions about copyright issues, please report us to resolve them. We are always happy to assist you.
Similar Documents
Share
Transcript
  INFORMATION TECHNOLOGY TUTORIAL OBJECTIVES TO COVER  : count counta countif COUNT & COUNTA You have already used a count  function such as =count(B3:B25). Note however that this function only works on cell ranges which contain numerical values. {Special note: cells can contain values of the types:- text (sometimes called labels), numbers or formulae}. To make a count on a range which contain text values, e.g. names or addresses, the count function will return a total of 0  (zero). To solve this problem, a =COUNTA  function is used. This tells the program that the values to be counted are of the text   type. The format for a counta  function is exactly the same as for a count: =counta(B3:B25).   Practice Session 1.   Open the workbook called Francis Jack Student File.xlsx . 2.   Delete the English marks for any four students. 3.   Perform a count to show how many students have English marks. Remember to add a suitable label to the left or above the cell where you will type your formula. 4.   Now you want to count the number of students in the class. You cannot use the subject English since all of them do not have marks recorded. So, you must count the actual students. To do this, use a COUNTA  on the students surname  or Christian name . Now you know how many students are in the class and how many have taken the subject English. COUNTIF While count & counta allows you to count all entities listed, you can also count only specified items in a list. To do this, a = countif   function is used. Like the count function, the countif function requires a specific range, but it also requires a criterion ( singular to criteria ). In other words, you tell the program what items you need to count. The format looks like this: =countif(range, ” criterion ” )      The range is the set of cells which contains the values you are interested in (e.g. B3:B58).    The criterion is a value such as green, male, >500, etc. Let ’ s try it on the worksheet 1.   You want to count the boys and the girls in the class. So you type: =countif(D2:D26, ” Male ” )  a.   Things to note  –   no spaces in the formula  b.   The quotes around the criterion c.   The opening and closing parentheses d.   The above will count male students only 2.   Check your result by physically counting the male students. 3.   You can now type the formula to count the girls in the class. 4.   Write the formula to count all the students who receive more than 50 marks in Math. {Note  –   you must use your math operators > more than < less than <> not equal to >= more than or equal to <= less than or equal to Note that > also means after and < also means before } 5.   Write the formula to count all the students who receive less than 65 marks in IT. 6.   Write a formula which will display the number of students who received at least   69 in Math. This formula requires you to use more than or equal to since the count must include the number 69. WILDCARDS At this stage we will introduce a feature which is used in searches and can be used to find files using Windows Explorer (on the harddrive or your flash drive). A wildcard (*) represents all missing letters from a search criterion. For example, if you forgot the name of your file which should have been MyEmployees.docx, you can search for My* or M*.docx. the search will produce all files which start with My  in the first case, and all MS Word files which start with M  is the second case. The instances of wildcards which you need to know are as follows: D* - all entities or files starting with D *W  –   all entities or files finishing with W *ga* - all entities or files which contain the letters ga. You can now apply these to your spreadsheet. Simple format looks like =countif(M5:M38, ” F*)  This will count only those values which begin with F. Practice 1.   Write a formula to count those students whose surname begins with B. 2.   Write a formula to count those students whose first name ends with a. 3.   Write a formula to count those students whose ID.s begins with 2009. 4.   Write a formula to count those students whose first name begins with C. 5.   Write a formula to count those students whose surname contains the letter a.   
We Need Your Support
Thank you for visiting our website and your interest in our free products and services. We are nonprofit website to share and download documents. To the running of this website, we need your help to support us.

Thanks to everyone for your continued support.

No, Thanks
SAVE OUR EARTH

We need your sign to support Project to invent "SMART AND CONTROLLABLE REFLECTIVE BALLOONS" to cover the Sun and Save Our Earth.

More details...

Sign Now!

We are very appreciated for your Prompt Action!

x