Exploration Formulas in Ms. Excel (SUM, AVERAGE, IF, COUNT, MAX, MIN, SUMIF, COUNTIF, and RANK)

In this project, an analysis of the turnover received by male employees and female employees will be carried out on probation in a company.

The currency used in this project is Rupiah (IDR).

Data source from Ignasius Ryan and I modified.

The data presents are as follows:

• SUM. Used to add/total data quantitatively. In this project, I want to see the total turnover of male employees and female employees. The formula for SUM:

Then the output will be as follows:

To see the total turnover of other male employees and female employees, it is done by dragging down, as follows:

• AVERAGE. Used to calculate the average on a data. In this project, we want to see the average turnover of each male employee and female employee on probation. Formula for AVERAGE:

Then the output will be as follows:

To see the average turnover of other male employees and female employees, it is done by dragging down, as follows:

• IF. Used to modify a statement.
  In this project, it is assumed that male employees and female employees who pass the probation are those who get a total turnover of more than equal to (>=) IDR 25,000,000. Then, the formula is written: =IF(The total turnover of one employee>=25000000>put a comma (,) and quotation marks(“ “) then write the first statement> put a comma (,) and quotation marks(“ “ ) then write the second statement.

• RANK. Used to determine the rank or order in the form of numbers in a data. In this project, the ranking of employees is measured based on the total turnover earned during the probation. The formula for RANK is as follows: =RANK>open brackets “(“>click one of the data in the total assets column>then drag all data on the Total Assets variable>FN+4(so that the data does not shift or freeze).

Then the output is as follows:

If you want to sort the ranking from largest to smallest then the steps are taken: right click>sort>Sort Smallest to Largest

• COUNTA. Used to add qualitative data

• MAX. used to see the largest value of a data. In this project, we want to see the largest turnover of male employees and female employees who undergo probation in terms of total turnover. Formula: =MAX(column you want to analyze)

Then it can be seen that the highest turnover is Rp. 39,250,000

• MIN. Used to see the smallest value of a data. In this project, we want to see the smallest turnover of male employees and female employees who undergo probation as seen from the total turnover. Formula: =MIN(column you want to analyze).

Then it can be seen that the lowest turnover is Rp. 21,000,000

• SUMIF. Used to add up a certain criterion on a quantitative value. In this project, we want to see the total turnover of male employees on probation which is calculated based on the total turnover. The formula used: =SUMIF(range of total number of employees, “criteria data” (in this case, male is represented by the letter L), total turnover range).

Then it can be seen that the total turnover of male employees is Rp 100,250,000

This formula also applies to analyzing the total turnover of female employees. For female employee criteria, it is denoted by the letter “P”.

So it can be seen that the turnover of female employees is IDR 45,000,000

• COUNTIF. Used to add up certain criteria on qualitative values in this project, want to count the number of employees who pass. Then the formula used: =SUMIF(range of data you want to analyze, data criteria).