Sunday 19 June 2016

Microsoft Excel: Calculating Aspect Ratios

You have downloaded a video file from internet (hopefully legally) and when you play it, something just doesn’t feel right; the picture looks squished (basketball athletes look short) or stretched (supermodels look plus-sized models). You want to know the video’s aspect ratio and compare to what it should be from the looks of it. Most players will readily tell you the resolution of a video but finding its aspect ratio can be tricky if not impossible. Sometimes the information given is just not reliable.

You can reliably calculate any video’s aspect ratio – provided that you already know its resolution – by using following formula in Microsoft Excel:

=A2/GCD($A$2,$B$2)

Or

=A2/GCD($A$2:$B$2)

The only difference between these two formulas is that the first one uses references to individual cells as indicated by comma and the second one uses a range of cells as indicated by the colon character. In each case, refernces include only two cells, A2 and B2. A2 contains width of the video and B2 contains its height.

How to Use

Let us say you have a small video with a resolution of 320x240 which means its height is 320 pixels and height is 240 pixels. You want to calculate aspect ration of this video. First look at following screen capture:


Start a new work sheet. Just like in the picture above, type 320 in A2 and 240 in B2.

In A3, type =A2/GCD($A$2,$B$2) and press Enter. Click on A3, copy this cell in B3. In formula bar, the formula will look like =B2/GCD($A$2,$B$2). If you notice only one reference was updated in the formula, that is B2. It is because $ sign in a reference makes it absolute and tells Excel not to change it when the formula containing it is copied.

In above example, aspect ratio of your video is 4:3 or 1.33:1 if you divide both numbers by 3.

Here is another example:


Try doing it yourself and use =A2/GCD($A$2:$B$2) for this example. Here, too, the aspect ratio is 4:3.

Non-standard Aspect Ratio

Poorly processed videos, and most pictures and photographs available online have non-standard aspect ratios like in following example:


As per above calculation a video or a picture with resolution of 854x480 has an aspect ratio of 427:240 or 1.78:1 if you divide both numbers by 240.

Desktop Wallpapers

Sometimes you download or take a really nice picture and you want to use it as your desktop wallpaper. You know that your operating system will stretch or shrink the image to fit it on your desktop, however, if the aspect ratio of the picture is not same as your monitor, it will look squished or stretched. To find out beforehand if the photograph that you want to use is appropriate for your monitor you can use same techniques in Excel described above. Let us look at an example:



In this hypothetical scenario, we have photograph with resolution 5312x2988. Such resolutions are common in modern digital photography. In this example, the computer user still has an old CRT monitor with 1024x768 resolution. Using above formulas, the user finds out that the picture has modern wide-screen aspect ratio of 16:9 and his monitor is 4:3, which means he must crop the photograph to make it look decent on his desktop. In other words, before using a picture as a wallpaper for your desktop, make sure that its aspect ratio is exactly the same as your monitor. Pictures of equal and larger resolution than that of your monitor make better wallpapers with crisper edges and tones, however, if their aspect ratios do not match your monitor the results can be a bit unsightly.

Simpler Calculation: Ratios with Decimals

There is a much simpler way of calculating aspect ratios which can be done with any hand-held electronic calculator as well as Microsoft Excel. Let us take two resolutions from last example above. In each of 5312x2988 and 1024x768 divide each number on both sides of x with smaller of the two. For example, in 5312x2988 divide both 5312 and 2988 by 2988 – smaller of the two – to get 1.78:1. You can get same result by dividing both sides of 16:9 by 9. Similarly, with 1024x768 and 4:3, you get 1.33:1.

Despite these ratios with decimals are easier to calculate and more readily referred to in many technical literatures, they are not easily understood by most people as decimal numbers are not strong suite for them, or these numbers are not as straight-forward as ratios involving integers only. Personally, I prefer non-decimal (integer) ratios over decimal ratios.

1 comment:

Anonymous said...

The function "GCD" does not exist in Excel 2016. Please explain!