PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES
Let us first discuss the scenario when we have a complete column filled with numbers and we need to find out the occurrences of some specific values.
The sample data is given below. These are the random numbers between 11 to 20.
16 | ||||
14 | ||||
16 | ||||
14 | ||||
15 | ||||
15 | ||||
12 | ||||
17 | ||||
18 | ||||
19 | ||||
18 | ||||
20 | ||||
17 | ||||
16 | ||||
20 | ||||
14 | ||||
12 | ||||
19 | ||||
12 | ||||
16 |
We’ll sort this problem out using the following methods
1. Using FREQUENCY FUNCTION.
2. Using UNIQUE AND COUNTIF.
First of all let us revise the FREQUENCY FUNCTION.
Frequency function returns the number of occurrences in a given class intervals. Although it is a statistical function but we can use it here.
The syntax of the frequency function is
=FREQUENCY (DATA , BIN ARRAY)
We need two arguments to be used in the frequency function.
One is the data, which we already have.
Other is the bin array or the intervals.
Now for this, as we have random numbers and the limit we know. We’ll make the array from 11 to 20 in a column which we call as bin array column as we have the numbers from 11 to 20.
Now suppose we need to find out the number from 11-15 and 16-20, our bin array will be 15,20
But here, we want all the number’s frequency, we’ll take all the numbers and the bin array will be 11,12,13,14,15,16,17,18,19,20 in a column.
First of all let us revise the UNIQUE FUNCTION.
UNIQUE FUNCTION returns a list of UNIQUE VARIABLES from the given list
or range. It filters out the repeated data and keeps the unique data.
SYNTAX:
=UNIQUE(ARRAY to be operated , by rows/columns , Exactly once /All)
The UNIQUE function has the following arguments:
ARRAY to be operated It is the array which needs to be searched for unique values.
by rows/columns [optional, default-false] if true- Search for repeated rows if false-search for repeated columns
Exactly once/All [optional, default-false] if True, will result only those entry which occur only once. If false, will result those entries also which occur more than once [ofcourse will return a single copy]
CLICK HERE TO GET FULL INFORMATION ABOUT UNIQUE FUNCTION.
COUNTIF:
COUNTIF FUNCTION returns the total number of cells from a given data set or range which fulfill a particular given criteria.
SYNTAX:
=COUNTIF(RANGE, CRITERIA)
RANGE Given range which needs to be scanned to find out the number of cells which fulfill the criteria
CRITERIA The criteria on the basis of which , cells will be counted.
NOTE* CRITERIA NEEDS TO BE GIVEN IN THE “”.
Following picture shows the final outcome of the table. We achieved what we wanted.
Let us first discuss the scenario .
We have a loan agency and we are disbursing loans to different customers. But due to some mistake , rather than keeping a proper accounts the entries were done in a random fashion.
Now we need to find out the number of entries and total loan against every customer in the ledger.
NAME | LOAN |
DANIEL | $1,322.00 |
ASHU | $1,306.00 |
RUTHERFORD | $1,029.00 |
DAISY | $1,154.00 |
BERENSON | $1,411.00 |
ASHU | $1,363.00 |
RUTHERFORD | $1,296.00 |
DAISY | $1,203.00 |
DANIEL | $1,274.00 |
RUTHERFORD | $1,200.00 |
DAISY | $1,377.00 |
ASHU | $1,267.00 |
DANIEL | $1,483.00 |
RUTHERFORD | $1,244.00 |
DAISY | $1,089.00 |
We’ll sort this problem out using the following methods
1. Using FREQUENCY FUNCTION. -NOT APPLICABLE AS FREQUENCY FUNCTION WORKS FOR NUMBERS ONLY BUT WE CAN PLAY WITH DATES IF WE HAVE DATES IN THE QUESTION.
2. Using UNIQUE AND COUNTIF. -YES, IT WOULD WORK.
First of all let us revise the the functions which are to be used so that we understand their importance in this context.
UNIQUE FUNCTION:
UNIQUE FUNCTION returns a list of UNIQUE VARIABLES from the given list
or range. It filters out the repeated data and keeps the unique data.
SYNTAX:
=UNIQUE(ARRAY to be operated , by rows/columns , Exactly once /All)
The UNIQUE function has the following arguments:
ARRAY to be operated It is the array which needs to be searched for unique values.
by rows/columns [optional, default-false] if true- Search for repeated rows if false-search for repeated columns
Exactly once/All [optional, default-false] if True, will result only those entry which occur only once. If false, will result those entries also which occur more than once [ofcourse will return a single copy]
CLICK HERE TO GET FULL INFORMATION ABOUT UNIQUE FUNCTION.
COUNTIF:
COUNTIF FUNCTION returns the total number of cells from a given data set or range which fulfill a particular given criteria.
SYNTAX:
=COUNTIF(RANGE, CRITERIA)
RANGE Given range which needs to be scanned to find out the number of cells which fulfill the criteria
CRITERIA The criteria on the basis of which , cells will be counted.
NOTE* CRITERIA NEEDS TO BE GIVEN IN THE “”.
CLICK HERE TO GET FULL INFORMATION ABOUT COUNTIF FUNCTION.
SUMIF FUNCTION:
SUMIF function adds us the
different given values or a range only if a particular criteria is met,
otherwise it rejects the calculation.
SYNTAX:
=SUMIF(LOOKUP_RANGE, CRITERIA, SUM_RANGE)
In the column G as shown in the picture below, we’ll try to find out the unique names of customers.
STEPS:
In the column H as shown in the picture below, we’ll try to find out the number of entries done against each unique customer.
STEPS:
In the column I as shown in the picture below, we’ll try to find out the total loan disbursed to each and every customer.
STEPS:
The following picture shows the final result of the problem.