Assignment 9 Mathematics and Logical Function Count Related Function

 

Mathematics and Logical

Count Related Function

Function Use

COUNTIF :- Countif function count the number of cell in a range, that meet a given criteria.

COUNTA:- CountA function to count cell that contain numbers, text , logical value, error values and empty text (" ") it means space.

COUNTIFS:- Countifs function is used for counting cell within a specific range that meet a certain criteria or condition. for example you can write a countifs formula to find out how many cell in your worksheet contain a number greater then or less than the number you specify.

COUNTBLANK:- This function is used to count blank cells in a range. where the word blank means empty.

COLUMNS :- Columns function return the column number for a reference.

ROW :- Row function return a row number for a reference.

SUM:- Sum function is built in function which add all the number in a range of cell and return the result of the addition.

IFERROR :- Iferror function manage error in formula and calculation. Iferror check a formula and if it evaluates to an error, returns another values you specify. otherwise return the result of the formula.

IF Function:- the IF function can perform a logical test and return one value for a "TRUE" result and another for a "FALSE" result.

ISEVEN:- The ISEVEN function return "TRUE" when a numeric value is even, and "FALSE" for odd numbers.

N Function:- N function converts a value to a number.

ISNUMBER :- This function check the value is number or not.

MOD:- MOD function return the reminder after a number is divided by a divisor.

1. How many cell contain "0" (Zero)

=COUNTIF(D3:H12,"0")

COUNTIF :- This function is use to count cell which is depend on criteria.

2. How many cell not blank

=COUNTA(D3:H12)

COUNTA :- Counta function count the number of cell that are not empty in a range.

3. How many cell have only 4 digit.

=COUNTIFS(D3:H12,">99")-COUNTIF(D3:H12,">=9999")

This function is use to count cell which is depend on criteria,

4. How many cell have only numeric value

=COUNT(D3:H12)

This function is use to count the number of cell that contain numbers.

5. How many cell contain text

=COUNTIF(D3:H12,"*")

This function count cell which is depend on criteria.

6. How many total cells are in the array

=COUNTA(D3:H12)+COUNTBLANK(D3:H12)

COUNTA:- this function count the number of cells that are not empty in a range.

COUNTBLANK :- count the number of empty cell in a range of cell.

7. How many total columns are in the array

=COLUMNS(D3:H12)

Columns function return the columns number of the given cell reference.

8. How many total Row are in the array

=ROW(D3:H12)

Return the row number of a reference in that array

9. How many cell contain Even number

{=SUM(--IFERROR(ISEVEN(IF(N(ISNUMBER($D$3:$H$12)),$D$3:$H$12," "))),0))}

SUM:- Sum function add the values.

(--) :- Double negative symbol convert to "TRUE" or "FALSE" value into "1" or "0".

IFERROR:- iferror function return a value you specify if a formula evaluates to an error otherwise it will be return of the formula.

ISEVEN :- find the even value in a range.

ISNUMBER:- Check the value is number or not.

N :- convert value into numeric.

IF:- test the logical test and check the function.

{} :- It is array function press ctrl + shift+ Enter.

10. How many cell have value which is divided by 5

{=SUM(--(IF(ISNUMBER($D$3:$H$12),MOD($D$3:$H$13,5),1)=0))}

SUM:- Sum function add the values.

(--) :- Double negative symbol convert to "TRUE" or "FALSE" value into "1" or "0".

IF:- test the logical test and check the function.

ISNUMBER:- Check the value is number or not.

MOD:- This function return the reminder of divisor by "5"

Note:-

This is an assignment related to count the different criteria.

there is one table is given named as array in that table there are some dummy number text are given as you can see in the array table.

From the question table you have to follow the different criteria and answer the question in yellow cell.

use any function and find the solution of all criteria which are mention.

By

Namrata