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.

Canada Post FlexDelivery: Review Appendant

This is second installment of (or shall we say a supplementary to) my earlier blog about Canada Post’s FlexDelivery feature.

After experimenting with some packages, delivered to my local post office, from different sellers I have come to the conclusion that unless a package is trackable with a tracking number, FlexDelivery system will not email you to notify that your package is ready for pick up at the post office.

Someone has mentioned in one of the comments under my previous blog on this topic that the packages ordered from abroad are not being notified about via email when they are at the chosen post office. In my experience, most sellers who send packages overseas – across the border in case of most of the US – they use the cheapest modes of shipping, which, in most cases, do not offer a tracking number; even if they do it is useful only within the country of origin of the shipment. The barcodes from other countries are not scanned by Canada Post.

My guess would be that if an overseas seller uses an expensive or more reliable shipping method, there is a good chance that FlexDelivery will notify the buyer about the arrival of his or her packages.

In my observation, when a good (read expensive) shipping method is used by a seller abroad, the parcels sent so, once they enter Canada, are given Canada Post’s own tracking number barcodes which will be scanned, unlike those issued outside Canada, at the time of delivery to the post office of buyer’s choice.

To put it simply, if your package is trackable in Canada, FlexDelivery will notify you by email about its arrival at your PO Box address. Otherwise you are liable to potentially lose your long awaited parcel if you do not obsessively keep checking with your post office either in person or by phone.

Microsoft Excel: Cricket: Convert Balls into Overs

In cricket, converting balls to overs is equally difficult as overs to balls when there are fractions of overs involved. If a given number of balls is a multiple of 6 (each over has 6 balls) a simple division is enough. If not, then you have to take the remainder and convert it into fraction part of resulting overs. For example, converting 19 balls to overs gives 3.1. Divide 19 by 6, answer is 3 and remainder is 1, hence, 3.1 overs.

Unfortunately, Microsoft Excel cannot handle this hybrid system of base 10 and base 6 mathematics that is the specialty of arithmetic of Cricket overs. To convert balls into overs in Excel use following formula:

=TRUNC(J17/6,0)+MOD(J17,6)/10

J17 is the address of cell containing balls. Following illustrates an example of this formula in action:

In above example, each yellow highlighted cell contains aforementioned formula. Turquoise highlighted cell contains formula explained here. Runs were added using a formula detailed here.

Microsoft Excel: Cricket: Convert Overs into Balls

In cricket, any digit after decimal point of a number representing overs is not fraction of overs but the balls of next incomplete over; for example: 3.2 overs means 3 overs and 2 balls. Converting 3 overs into balls with or without Microsoft Excel is simple. All you have to do is multiply 3 by 6 which would be 18 balls. To convert 3.2 overs into balls, first you multiply 3 by 6 to get 18, then add 2 to get 20. This can be a little challenging in Microsoft Excel too. To make it simple, use following formula:

=INT(J2)*6+(J2-INT(J2))*10

J2 is the address of cell containing overs with or without fraction. Following illustrates an example of this formula in action:

In above example, each yellow highlighted cell contains aforementioned formula. Turquoise highlighted cell contains formula explained here. Runs were added using a formula detailed here.

Microsoft Excel: Convert Centimeters and Meters to Feet and Inches

Microsoft Excel does not have a built-in function to convert any measurement of length into inches and feet with single and double quote. Therefore, I have following formula that can convert any length specified in centimeters into feet and inches of x' x" format:

=TRUNC(B3/2.54/12)&"' "&TRUNC(MOD(B3/2.54,12))&""""

B3 is the address of cell containing centimeters.

Practical example:

Similarly, to convert meters into feet and inches of x' x" format use following formula:

=TRUNC(B8*100/2.54/12)&"' "&TRUNC(MOD(B8*100/2.54,12))&""""

B8 is the address of cell containing meters.

Practical example:

Warning: There are two disadvantages of above two formulas. First, results (6' 1" & 5' 3") are in text format rather than numerical format, therefore, they cannot be used in further calculations. Second, for simplicity sake, fractions of inches are simply dropped, not rounded off, but dropped.

Following formulas will round off inches to the nearest integer:

cm to ft' in"

=TRUNC(B3/2.54/12)&"' "&ROUND(MOD(B3/2.54,12),0)&""""

m to ft' in"

=TRUNC(B8*100/2.54/12)&"' "&ROUND(MOD(B8*100/2.54,12),0)&""""

Microsoft Excel: Adding Overs in Cricket

Those of you who know the gentlemen's game are aware of the fact when talking about overs, the number after the decimal point always indicates balls that have been bowled. For example: 6.5 overs means 6 overs and 5 balls, 11.2 overs means 11 overs and 2 balls, and so on so forth.

Since each over has 6 balls, here is how mathematics of cricket overs works:

 

In first line, we are adding 1 ball to 6 overs and 5 balls resulting in 7 complete overs. In second line, we are adding 4 balls to 11 overs and 2 balls resulting in 12 complete overs. In third line - that's where things get a little interesting - we are adding 2 overs and 3 balls to 5 overs and 4 balls. Adding 3 and 4 gives us 7. Since an over cannot have more than 6 balls, we mentally subtract 6 from seven and get 1. We turn 6 balls - that we just subtracted - into 1 over and carry it over to other side of the decimal point and add it to 5 and 2. Using the example of elementary arithmetic we get:

This seems like a hybrid of base 10 and base 6 mathematics. Since base 6 is not built into Microsoft Excel, doing this kind of addition can be challenging, if not impossible, in a worksheet.

You can use following rather complex formula to add overs of multiple players in a score card:

=SUM(TRUNC(C2:C10,0))+TRUNC(SUM(C2:C10-TRUNC(C2:C10,0))*10/6)+MOD(SUM(C2:C10-TRUNC(C2:C10,0))*10,6)/10

C2:C10 is the range of cells that contains overs that you want to add. Here is this formula in action:

Another slightly modified example:

Remember: Above formula is of a special kind called array formula. After you have typed or copied this formula in your worksheet, press CTRL+SHIFT+ENTER to calculate. Every time you edit this formula, you will have to use same three keys to recalculate.

Microsoft Excel: Sum of Cricket Runs with Not Out Asterisk

If you are trying to add runs of individual batsmen of a cricket team using Microsoft Excel, you soon realize that the software skips the score with asterisk (example: 32*) indicating not out. The reason behind this is that Excel treats the value of cell containing a number and asterisk as text, therefore, does not include it in SUM function.

Following formula can solve this problem:

=SUM(B2:B9)+LEFT(B10,LEN(B10)-1)

B2 is the cell containing runs of first batsman and B9 contains the score of second last batsman. B10 contains the score of last batsman who is not out. This cell has asterisk with a numeric value.

Here is an example of this formula in action:

The total of 174 was acquired by using above formula.

Warning: For a scorecard where all batsmen are out use following simple formula:

 =SUM(B2:B10)

Using this simple formula in above example will result in false total of 171.

Let us take a look at more complex situation on a score card. This is most common one. At the end of a match we usually have two batsmen who are not out, hence, have asterisks with their runs. For such a situation, use following formula to add runs of all batsmen:

=SUM(IF(RIGHT(B2:B10,1)="*",LEFT(B2:B10,LEN(B2:B10)-1)*1,B2:B10))

Remember, this is not a simple formula. It is a special kind of formula called array formula. Here is how you use it.

B2 is the first cell in the range containing list of scores and B10 is the last. As always, you'll have to change it to the range of cells that you are using. After you have typed or copied this formula in your worksheet press CTRL+SHIFT+ENTER to calculate. In the formula bar, the formula will look like this:

{=SUM(IF(RIGHT(B2:B10,1)="*",LEFT(B2:B10,LEN(B2:B10)-1)*1,B2:B10))}

Important: Every time you edit this formula, use CTRL+SHIFT+ENTER to update the results.

Here are two examples of this formula in action:

Example 1:

Notice that the positions of two not out batsmen don't affect the calculation.

Example 2:

Here B2:B10 was used as range. Notice that blank cells also don't affect the calculation.