How to Calculate the Average Ratio in Excel – 2 Methods

The sample Dataset showcases Products, Sales in January, and in February.

Method 1 – Using the GCD Function to Calculate the Average Ratio

Use the AVERAGE Function to find the value and the GCD Function to return the greatest common divisor for integer values.

Steps:

• Insert two additional columns: GCD and Ratio.

• To find the GCD, enter the following formula in a cell. Here, E5.

• Press ENTER.

• Drag down the Fill Handle to see the result in the rest of the cells.

• Use the following formula to find the Ratio:

Values in C5 and D5 are divided by the greatest common divisor and presented as a ratio (values are concatenated using a colon).

• Press ENTER to see the Ratio.

• Drag down the Fill Handle to see the result in the rest of the cells.

To calculate the average:

• Use the following formula to get the Average Sales in January:

• Press ENTER to see the Average Sales in January.

• Use the same formula to find the Average Sales in February:

• Press ENTER.

• To find the Greatest Common Divisor between the Average Sales in January and the Average Sales in February use the following equation:

• Press ENTER.

• Use the formula below to find the Average Ratio:

• Press ENTER.

Read More: How to Calculate Ratio of 3 Numbers in Excel

Method 2 – Combining the ISNUMBER, IF and AVERAGE Functions

The ISNUMBER Function checks if a cell contains a number. The IF Function verifies if the value in the cell meets the condition. The AVERAGE Function returns the average of the arguments.

Steps:

• Select a cell to enter the formula. Here, C11.

• Press ENTER to see the Average Ratio.

Practice Section

Practice here.

Download Practice Workbook

Related Articles

• How to Calculate Male Female Ratio in Excel
• How to Calculate Sortino Ratio in Excel
• How to Calculate Sharpe Ratio in Excel
• How to Calculate Odds Ratio in Excel
• How to Calculate Compa Ratio in Excel

<< Go Back to Ratio in Excel | Calculate in Excel | Learn Excel