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

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

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