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(value or range)

=MAX(value or range)

=MIN(value or range)

=SUMIF(range, criterion, [sum_range])

=SUMIFS(sum_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

  1. The number of companies

  2. Total revenue generated across companies

  3. Average revenue generated across companies

  4. Max revenue generated by a companies

  5. Min revenue generated by a companies

  6. Revenue per employee

  7. Average revenue generated across companies in the United States and China

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

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

Spreadsheet Generic Formula

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

TAGS:How to use SUM formula in ExcelHow to use the AVERAGE formula in ExcelHow to use the COUNT formula in ExcelHow to use COUNTA formula in ExcelHow to use MAX formula in ExcelHow to use MIN formula in ExcelHow to use the AVERAGEIFS formula in ExcelHow to use SUMIFS formula in ExcelHow to use the AVERAGEIFS formula in Excel
TAGS:How to use SUM formula in Google SheetsHow to use the AVERAGE formula in Google SheetsHow to use the COUNT formula in Google SheetsHow to use COUNTA formula in Google SheetsHow to use MAX formula in Google SheetsHow to use MIN formula in Google SheetsHow to use the AVERAGEIFS formula in Google SheetsHow to use SUMIFS formula in Google SheetsHow to use the AVERAGEIFS formula in Google Sheets