With the advent of HDTV, all the reviewers talk about the new 16:9 screen ratio as opposed to the old - square - 4:3 ratio.
I always thought widescreen TV and widescreen computers were the same thing. Until I was trying to design a PowerPoint(r) presentation to show on a TV. I set the page size to 16x9 and it didn't fill my computer quite right. Then I looked at the actual resolutions.
HDTV is nominally 1920 x 1080 (pixels). My computer monitor is 1440 x 900. I could quickly divide and see that HDTV is 1.78:1 while the computer is 1.6:1. But how do these ratios compare - in integers?
I'll save you the suspense. HDTV is, in fact, 16:9. The computer turned out to be 16:10 - a little squarer so that a presentation to fill the TV will leave a little top and bottom on the computer.
Google Docs and Excel(r) have an advanced* function called =GCD - "Greatest Common Denominator". We all learned about the Least Common Denominator in the 4th grade, but what is GCD? It's the largest integer that will evenly divide both sides of a fraction. If you divide both sides of a fraction by the GCD, you get what I was looking for: a simple integer ratio.
You can download the worksheet+ as a picture or live Google Doc that you can use as your own. Either way, column J contains the formula (without the ' ) to fill in under the raw data.
* GCD may not work in Excel until you load the Add-In. From the Tools menu, select Add-Ins... and check Analysis ToolPak. When you click OK, it may ask for your original Office CD to load the component.
Saturday, August 18, 2007
Wednesday, August 8, 2007
Miles per Gallon
OK, maybe I'm compulsive about keeping trivial records. But with $3+/gal gas, everyone's starting to be interested in how they're doing day-to-day.
This workbook+ contains one simple formula =(F8-F7)/E8 [ (mileage this fillup)-(mileage last fillup)/(gals this fillup) ] in col G to calculate MPG at your last fillup. Since you may not be consistent in how full you get your tank, I also included 2-tank and 3-tank averages to smooth it out.
My Audit column recalculates the cost as the price per gallon times the gallons to catch typos. If it's off by more than a few cents (to allow for the hundredths of a gallon), one of the numbers is wrong. Sometimes I know the audit is going to be invalid if I didn't actually fill up once and a "single tank" represents multiple purchases at different prices (this is common if I know I'm travelling to a low-price area, but need another 2 gallons to get there).
For MPG tracking to be accurate, you have to be faithful. If you regularly buy gas without filling up, you have to combine individual purchases to create a "tank" that you know all the miles were burned on.* And, if you forget to write down the gallons just once, not only is that tank invalid, but so is all your cumulative data. Like I said, I'm compulsive.
I haven't yet figured the simple formula (I can do it with programming) for row 1 to get an accurate average of all the non-0 entries in a column. If I set the average past the current bottom of the worksheet, it counts all the empty rows. As it is now, my "lifetime" is only to where I was last time I wrote the formula.
* If you don't fill up ...
You can only calculate MPG if you know exactly how many gallons you burned for a given number of miles. If you can only afford $5 at a time; one day that $5 may bring you up to 7/8 full and the next you leave the pump just 1/3 full. That means you burned more gas than you bought, but you don't know how much. If you occasionally miss a fill up, just add all the partials to the next time you top off and call it one "tank". If you never fill up ... well, your lifetime average will be close (because being off by 4 gal when you're calculating MPG for 400 gal isn't significant).
(c) 2007 Bill BarnesMore like this: http://3500a.blogspot.com/
+ To use the published workbook:Click the link to open a web picture - not a working spreadsheet - of my workbook. Type the content from the Formulas page into a spreadsheet. The heading "Date" goes in cell B3. You only really need the five formulas on row 7 that start =(F7-F6)/E7. Then you can use your spreadsheet's autofill function to replicate them on the rest of your rows. Be sure not to type the " ' " before the "=".
Even easier; if you have a Google account, you can open a live spreadsheet in Google Docs. From there you can export it for your own use with the formulas intact.
This workbook+ contains one simple formula =(F8-F7)/E8 [ (mileage this fillup)-(mileage last fillup)/(gals this fillup) ] in col G to calculate MPG at your last fillup. Since you may not be consistent in how full you get your tank, I also included 2-tank and 3-tank averages to smooth it out.
My Audit column recalculates the cost as the price per gallon times the gallons to catch typos. If it's off by more than a few cents (to allow for the hundredths of a gallon), one of the numbers is wrong. Sometimes I know the audit is going to be invalid if I didn't actually fill up once and a "single tank" represents multiple purchases at different prices (this is common if I know I'm travelling to a low-price area, but need another 2 gallons to get there).
For MPG tracking to be accurate, you have to be faithful. If you regularly buy gas without filling up, you have to combine individual purchases to create a "tank" that you know all the miles were burned on.* And, if you forget to write down the gallons just once, not only is that tank invalid, but so is all your cumulative data. Like I said, I'm compulsive.
I haven't yet figured the simple formula (I can do it with programming) for row 1 to get an accurate average of all the non-0 entries in a column. If I set the average past the current bottom of the worksheet, it counts all the empty rows. As it is now, my "lifetime" is only to where I was last time I wrote the formula.
* If you don't fill up ...
You can only calculate MPG if you know exactly how many gallons you burned for a given number of miles. If you can only afford $5 at a time; one day that $5 may bring you up to 7/8 full and the next you leave the pump just 1/3 full. That means you burned more gas than you bought, but you don't know how much. If you occasionally miss a fill up, just add all the partials to the next time you top off and call it one "tank". If you never fill up ... well, your lifetime average will be close (because being off by 4 gal when you're calculating MPG for 400 gal isn't significant).
(c) 2007 Bill BarnesMore like this: http://3500a.blogspot.com/
+ To use the published workbook:Click the link to open a web picture - not a working spreadsheet - of my workbook. Type the content from the Formulas page into a spreadsheet. The heading "Date" goes in cell B3. You only really need the five formulas on row 7 that start =(F7-F6)/E7. Then you can use your spreadsheet's autofill function to replicate them on the rest of your rows. Be sure not to type the " ' " before the "=".
Even easier; if you have a Google account, you can open a live spreadsheet in Google Docs. From there you can export it for your own use with the formulas intact.
Subscribe to:
Posts (Atom)