# Generic Formulas

For this exercise, we will analyse the world's top 20 companies by 2020 revenue and will be using SUM, AVERAGE, COUNT, MAX, MIN, SUMIF, SUMIFS, AVERAGEIF, and AVERAGEIFS functions in Google Sheets and all formulas will work with Microsoft Excel as well.

**=SUM(****value or range****)**

**=AVERAGE(****value or range****)**

**=COUNT(****v****alue or range****)**

**=MAX(****value or range****)**

**=MIN(****value or range****)**

**=SUMIF(****range****, criterion, [sum_range]****)**

**=SUMIFS(****s****um_range****, criteria_range1, criterion1, [criteria_range2, …], [criterion2, …]****)**

**=AVERAGEIF(****criteria_range****, criterion, [average_range]****)**

**=AVERAGEIFS(****average_range****, criteria_range1, criterion1, [criteria_range2, …], [criterion2, …]****)**

**What we can answer using these formulas and will focus on revenue and the same method and formula structure can be used for profit and you can try as an exercise. ****Download/access date used for this exercise ****here**

The number of companies

Total revenue generated across companies

Average revenue generated across companies

Max revenue generated by a companies

Min revenue generated by a companies

Revenue per employee

Average revenue generated across companies in the United States and China

Total revenue generated by the oil industry in the United States and China

Average revenue generated by the oil industry in the United States and China

**Let's count the number of company we have in the above data set**

Noticed I used Column D, not Column B OR C, the reason the **COUNT **counts all numeric values in a data set, including those which appear more than once. If we use the **COUNT **formula in Column B we get 0 as a result below. If your data set contains both text and numeric you can use the **COUNTA** formula. We also have a data label so we neee to subtracted 1 in the **COUNTA **formula below.

**=COUNT(****D:D****)**// counts all numeric values in a data set**=COUNTA(****A****:****A****)**// counts both numeric and text

**What did we find:**

**We have a total of 20 companies in the above data set**

**Let's find out the total revenue generated by all 20 companies**

We can find total revenue generated by using the **SUM **formula referring to Column D and the final formula look like this: **=****SUM****(****D****:****D****)**. You can also use multiple ranges like **SUM(A1:A2, F1:F2)**

**D****:****D****= value or range****// revenue column in the data set**

**What did we find:**

Total $5,955,725 million revenue by 20 companies in 2020

**Let's find out the average revenue generated by 20 companies**

We can find average revenue generated by using the **AVERAGE **formula referring to Column D and the final formula look like this: **=****AVERAGE****(****D****:****D****)**. You can also use multiple ranges like **AVERAGE(A1:A2, F1:F2)**

**D****:****D****= value or range****// revenue column in the data set**

**What did we find:**

On average each company generated $297,786 million in revenue in 2020

**Let's find out the MAX and MIN revenue generated**

We can find MAX and MIN revenue generated by **MAX **and **MIN **formula referring to Column D and the final formula look like this: **=****MAX****(****D****:****D****)** and **=****MIN****(****D****:****D****)**. You can also use multiple ranges like **MAX(A1:A2, F1:F2)**, **MIN(A1:A2, F1:F2)**

**D****:****D****= value or range****// revenue column in the data set**

**What did we find: **

The maximum revenue generated by a company is $548,743 million

The minimum revenue generated by a company is $193,346 million

**Let's find out the total number of employees**

We can find total employees by using the **SUM **formula referring to Column F and the final formula look like this: **=****SUM****(****F****:****F****)**. You can also use multiple ranges like **SUM(A1:A2, F1:F2)**

**F****:****F****= value or range****// employees column in the data set**

**What did we find:**

Total 9,723,060 people employed by 20 companies

**Let's find out the revenue generated per employee**

We can find revenue generated per employee by using formula below

**Revenue Per Employee = Total Revenue/Total Employees**

**What did we find: **

$0.61 revenue generated per employee across companies

**Let's find out the average revenue generated across companies in the United States and China**

We can find average revenue generated by using the **AVERAGEIF **and the final formula look like this:

**=AVERAGEIF(****$G:$G****,****$J2****,****D:D****)**

**$G:$G****= criteria_range****// country column in the data set****$J2****= criterion****// individual country****D:D****= average_range****// revenue column in the data set**

**What did we find:**

Average revenue generated across companies in the United States: $307,357 million

Average revenue generated across companies in China: $343,971 million

**Let's find out the total revenue generated by the oil industry in the United States and China**

We can find total revenue generated by using the **SUMIFS** formula referring to Column D and the final formula look like this:

**=SUMIFS(****D:D****,****$G:$G****,****$J2****,****$C:$C****,****$K2****)**

**D:D****= sum_range****// revenue column in the data set****$G:$G****= criteria_range1****// country column in the data set****$J2****= criterion1****// individual country****$C:$C****= criteria_range2****// industry column in the data set****$K2****= criterion2****// individual industry**

**What did we find:**

Total revenue generated by the oil industry in the United States: $264,938 million

Total revenue generated by the oil industry in China: $786,139 million

**Let's find out the average revenue generated by the oil industry in the United States and China**

We can find average** **revenue generated by using the **AVERAGEIFS **formula referring to Column D and the final formula look like this:

**=AVERAGEIFS(****D:D****,****$G:$G****,****$J2****,****$C:$C****,****$K2****)**

**D:D****= sum_range****// revenue column in the data set****$G:$G****= criteria_range1****// country column in the data set****$J2****= criterion1****// individual country****$C:$C****= criteria_range2****// industry column in the data set****$K2****= criterion2****// individual industry**

**What did we find:**

Average revenue generated by the oil industry in the United States: $264,938 million

Average revenue generated by the oil industry in China: $393,070 million

As I mentioned earlier, all formulas are compatible with Microsoft Excel as well and can be analyzed exactly as it analyses in Google Sheets.

**Download/access date used for this exercise ****here**