How to calculate wmape in excel

Ratio in Excel

Calculate Excel Ratio (Table of Contents)

  • Ratio in Excel
  • How to Calculate Ratio in Excel
  • Advantages of the calculation ratio in Excel

Ratio in Excel

A ratio is a way of comparing two sets of data to determine which data is larger or smaller than which. There is also the part between 2 parameters or numbers. This way we can compare the two sets of data.

How to Calculate Ratio in Excel

Calculating the ratio in Excel is very simple and easy. Let's use some examples to understand how the ratio is calculated in Excel.

You can download this Ratio Excel Template here - Ratio Excel Template

Calculate the ratio in Excel - example 1

A calculation ratio in Excel is simple, but we need to understand the logic for it. Here we have 2 parameters A and B. A has a value of 10 and B has a value of 20 as shown below.

And for that we use a colon (":") as a separator. To do this, go to the cell in which you want the output to appear. Enter the equal sign ( = ) to switch to edit mode for this cell. In the first syntax, divide cell A2 by B2, and in the second syntax, divide cell B2 by B2 itself. This is how we create the short comparison values ​​with a short ratio value as shown below.

Note: & is used to concatenate the syntax, and the colon (:) is the ratio symbol defined between the concatenation formulas.

When you have finished preparing the syntax, press Enter to see the result shown below.

As we can see above, the calculated value of the ratio is 0, 5: 1 . This value can be further modified to look better by multiplying the obtained ratio by 2 to get a full exact value, not in fractions, or we can keep the value as it was originally.

Calculate the ratio in Excel - example 2

There is another method of calculating the ratio in Excel. We're going to look at the same data that we looked at in Example 1. When we see the syntax we used in Example 1, we have divided cell B2 by B2 as shown below.

Dividing cell B2 by B2 or any cell with the same value of that cell will get the output as 1. The ratio obtained in Example-1 is 0.5: 1, where value 1 is a division by 20/20 if we divide the syntax mathematically. And 0.5 is the division of 10/20. That is, if in the second syntax (or the second half of the syntax) we default to 1 maintained, we finally get the value 1 to compare to the first syntax (or the first half of the syntax)

Now let's apply that logic to the same data and see what result we get.

To do this, go to the cell where you need to see the output and enter the "=" sign. Now, in the first half of the syntax, divide cell A2 by B2 and enter just "1" in the second half of the syntax as shown below.

As we can see in the screenshot above, we kept "1" in the second half of the syntax to get the same value as in Example-1. Now press Enter to see the result.

As we can see in the following screenshot, the result obtained is the same that we received in Example-1.

Now check both formulas applied in Example 1 and Example 2 and test them with a different value in both syntaxes. For testing we kept 44 in cells A2 and A3 and 50 in cells B2 and B3. With both methods of calculating the ratio, we get the same Excel value as shown below.

Calculate the ratio in Excel - example 3

There is another method of calculating the ratio in Excel. This method is a bit complex with short syntax. There is a format in Excel for determining the largest common divisors . So if you select 2 values ​​and compare with the third, the largest value from which the comparison value is divided is automatically taken into account. We did this manually in example-1 and example-2. For this, too, we will consider the same data that we saw in the examples above.

Now go to the cell where you want the result to appear and enter the "=" character. Now select cell A2 and divide it with GCD. And in GCD you mark both cells A2 and B2. The same procedure applies to the second half of the syntax. Highlight cell B2 and divide with GCD. In GCD, select both cells A2 and B2 separated by a comma (,), as shown below.

Note: & is used to concatenate the syntax, and the colon (:) is the ratio symbol defined between the concatenation formulas

Then press Enter to see the result shown below.

As we can see above, the calculated ratio is 1: 2. In practice, the ratio calculated in Example 1 and Example 2 is half the ratio found in Example 3, but has the same meaning in reality. Both values ​​have the same characteristics.

Let's not understand the GCD function. GCD or Greatest Common Divisor in Excel automatically determine the largest value in order to divide it by the value present in the counter. Finally give the same result as in the methods used in Example 1 and Example 2.

advantages

  • The methods shown in all examples are easy to use.
  • The syntax and value entered are easy to understand.
  • We can change the syntax according to our requirements.
  • If there are more than 3 values ​​that need to be compared, we can use the same formula.
  • A good thing about calculating the ratio using GCD in Excel is that it gives a result that looks good in terms of numeric signs and numbers as well.

Things to remember

  • Make sure the chaining is done properly to avoid mistakes.
  • We can use the correct CONCATENATION formula instead of using & as a separator.
  • A value obtained in Examples 1 and 2 and Example 3 appears to be different, but technically there is no difference in any of the ratio values.

Recommended articles

This was a tutorial on how to calculate the ratio in Excel. Here we discuss how to calculate the ratio in Excel, along with practical examples and a downloadable Excel template. You can also go through our other item suggestions -

  1. How do I use Excel PV Formula?
  2. Calculate with the FV function in Excel
  3. CONCATENATE function in Excel
  4. Simple formula in Excel