Description

Description:

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