How smart is the QF points calculator

[anat0l]

Basically it reduces down to:

Points Awarded = MAX(BIS miles * Base Multiplier * (1 + Status Loading + Class Loading), MPG)

where MPG is determined in a rather complicated fashion:

• If Airline = QF, 1000 for Y/Y+, 1250 for J/F
• If Airline = JQ, 1000 for JetFlex, 1250 for Star Class
• If Airline = BA, 125 for dY, else 500
• If Airline = AA, 500

It's times like this you wish there were a more elegant way to write conditionals in Excel formula (viz. in programming using case conditionals vs. nested if-then-else statements)

 

[thewinchester]

It's times like this you wish there were a more elegant way to write conditionals in Excel formula (viz. in programming using case conditionals vs. nested if-then-else statements)

Amen to that. If any of you take a look at how the spreadsheet is structured sometime, you'll see just how nasty this can be. To say my excel knowledge has been put to the test designing this would be an understatement.

To do all the complex calculations for different airline MPG's and earning ratios (which I'm working on at the moment), I've basically got one column which is doing the lookup to get the correct value or multiplier, sometimes with vlookups, others a complex index match formula, then another to work out the result.

It may not be the nicest way to do it, but given Excel's limitations it does the job nicely.

 

[anat0l]

To do all the complex calculations for different airline MPG's and earning ratios (which I'm working on at the moment), I've basically got one column which is doing the lookup to get the correct value or multiplier, sometimes with vlookups, others a complex index match formula, then another to work out the result.

It may not be the nicest way to do it, but given Excel's limitations it does the job nicely.

Au contraire; it is one of the nice Excel tricks there are out there, and if used well it helps reduce programming complexity, chance of error and debugging time.

The hidden column trick may look clumsy but there are several easy ways to cover up the evidence.

 

[medhead]

I've only just started playing with the calculator, haven't looked into hidden column and such. But wouldn't a lookup be simpler to use a lookup table type thing for MPG.

Also column Z isn't working right. I'm silver on a retain, but the formula in retain column for the first row puts me back to a bronze - "if SC<300, bronze" - then second row then has an opening status of bronze.

should the Z column formula be something like

=IF ($X11<700,K11,
(IF K11<> "platinum" and $X11<1400,"gold",
(IF $X11<1400,K11,"Platinum")))

I don't know that probably isn't right. But a couple of points the retain column has to be at least Silver. I've probably completely missed the point of the "Mode". But to me I'm both trying to retain and also qualify. Perhaps you need to change K column to take the highest status between Qualify and retain column (Y and Z) as the above attempt at a formula is complicated by the need to accomodate both the retain SC and the qualify SC for the next level.

Still full marks for putting this together.

 

[medhead]

To do all the complex calculations for different airline MPG's and earning ratios (which I'm working on at the moment), I've basically got one column which is doing the lookup to get the correct value or multiplier, sometimes with vlookups, others a complex index match formula, then another to work out the result.

It may not be the nicest way to do it, but given Excel's limitations it does the job nicely.

The index lookup is how I've done my investment property spreadsheet, to correctly group everything for tax time. But I'm certainly no excel wizard and I wouldn't ahve attempted what you've done here.

 

[thewinchester]

Au contraire; it is one of the nice Excel tricks there are out there, and if used well it helps reduce programming complexity, chance of error and debugging time.

The hidden column trick may look clumsy but there are several easy ways to cover up the evidence.

Yes, it is one of those fun excel tricks, but trying to use Excel as for relational database purposes will thin the hairlines of even the most dedicated person.

I've only just started playing with the calculator, haven't looked into hidden column and such. But wouldn't a lookup be simpler to use a lookup table type thing for MPG.

MPG is done using a lookup table, in two parts. Firstly it looks at the carrier and class to find a relevant MPG. It then subtracts points already earned (still to be revised for correct order of calculation as Serfty has already identified) to give you the additional MPG points.

Also column Z isn't working right. I'm silver on a retain, but the formula in retain column for the first row puts me back to a bronze - "if SC<300, bronze" - then second row then has an opening status of bronze...

Thanks medhead, and you're right on with that status calculation and the method behind the mode of qualify/retain. I'll revise how that's done as soon as I've finished building the lookup table for carrier earning ratios.

A question on presenting carrier earning ratios for everyone...

On the subject of the carrier earning ratios, it's a bit of a nightmare (I'm looking at you BA, AA and JL). To deal with this, the spreadsheet layout will remain exactly the same - but the options to select from in the booked travel class column will change slightly.

Once you have selected the carrier, the system will lookup the possible travel classes for that carrier code to populate the booked travel class column.

For example, if your carrier was CX, the booked travel class picklist would contain:

• Discount economy BHKLMV
• Economy Y
• Business CDIJ
• First AF

For a more complex example with BA, the list would return;

• Discount economy GKLMNOQSV all else
• Discount economy GKLMNOQSV Aust to Europe via BKK/SIN
• Economy BHY
• World Traveller Plus TEW
• Business CDIRJ
• First AF

Or for NC (Cobham, fmr. National Jet);

• Economy YKMQ Except PER-RVT
• Economy YKMQ PER-RVT
• Business J

Does this seems sensible to you all, specially the fact it gives the relevant class codes and clues to help distinguish the right travel class to select?

 

[medhead]

MPG is done using a lookup table, in two parts. Firstly it looks at the carrier and class to find a relevant MPG. It then subtracts points already earned (still to be revised for correct order of calculation as Serfty has already identified) to give you the additional MPG points.

Would it be easier to do a comparison of the MPG with the points earnt. something like

If PE>MPG, then PE, else MPG

I still haven't checked the hidden MPG column, but I assume it stores the MPG from the first step lookup of carrier and class.

 

[drewbles]

Thanks anat0l. I started working on this spreadsheet via my mac, and one of the things Microsoft took out of Office 2008 for Mac was VBA and macro creation. Then again, I could have easily worked on it via my Windows VM and could have knocked a bit more of that on the head.

You caught a few things I forgot to factor in which I was thinking about, will be going away and having some fun with these in V0.2

Will post back here soon when the next version is ready.

VBA and macros were re-added in a service pack. I use them every day on office for mac 08

Also, Great Circle Mapper 2.0 is due within the next week. Not sure what changes are afoot, but they are apparently! Check out: Great Circle Mapper | Facebook

 

[thewinchester]

Would it be easier to do a comparison of the MPG with the points earnt. something like

If PE>MPG, then PE, else MPG

I still haven't checked the hidden MPG column, but I assume it stores the MPG from the first step lookup of carrier and class.

There is a column which performs a lookup against the mpg worksheet, which looks for the carrier, then class of travel to determine the relevant MPG value. I built it this way in case MPG's are applied to other carriers in future.. a stitch in time and all that jazz.

From there, a nested IF kicks in within a hidden column, called difference. If value of MPG is greater than 0, test to see if MPG value is greater than the sum of base + cabin + status. If so, difference = mpg - base - cabin - status (the formula is a lot shorter as there's some sub-total columns hidden away to keep it easy to write and maintain).

VBA and macros were re-added in a service pack. I use them every day on office for mac 08

Have PM'd you a question about this. Also, thanks for the info on GCM 2.0.http://www.facebook.com/gcmap

 

[medhead]

There is a column which performs a lookup against the mpg worksheet, which looks for the carrier, then class of travel to determine the relevant MPG value. I built it this way in case MPG's are applied to other carriers in future.. a stitch in time and all that jazz.

From there, a nested IF kicks in within a hidden column, called difference. If value of MPG is greater than 0, test to see if MPG value is greater than the sum of base + cabin + status. If so, difference = mpg - base - cabin - status (the formula is a lot shorter as there's some sub-total columns hidden away to keep it easy to write and maintain).

I think what I'm suggesting is once you look up the MPG in column L, you don't need the stuff in columns M and N. Column S can in fact contain the IF statement from column M, but it only needs to compare the MPG against the Base miles + bonus miles and then assign the appropriate value. It possibly doesn't even need to be a nested IF, in this case.

 

[thewinchester]

Well, work continues and I've just finished V0.3 of this beast.

I've made some improvements based on anat0l's suggestions and fixed up some other things from the second version.

Improvements specific to V0.3

• Minimum points guarantee (MPG) calculation order issue fixed.
• Value proposition fields ($/sc, $/point) added, along with tracking of per-sector costs.
• Clean-up of information display.
• Print layouts for calculator pages finalised, will print without problems on a single page.

Features

Currently the spreadsheet will:

• Allow you to enter/select the airline, departure and arrival airport, cabin class and number of miles flown.
  • If number of miles is unknown, a single click will pass information on that sector thru to Great Circle Mapper to get the estimated mileage.
• Ensure you've entered in the valid arrival and departure airport codes
• Calculate miles based on the earning ratio you specify (this is soon to be automated to pre-fill with earning ratio for airline and cabin class specified)
  • If flying a carrier in a cabin class where SC's aren't earned, a zero value for all fields after earning ratio while still ensuring rest of spreadsheet calculations remain valid.
• Work out if a minimum points guarantee is acceptable to the carrier, and apply accordingly based on the airline and cabin class.
• Calculate if status credits, cabin bonuses and status bonuses are earned and apply accordingly.
• Provide a running total of miles and SC's earned.
• With sector cost information entered, provide some decision-making information

Feel free to download the spreadsheet (MS Excel, 326kb) and let me know what you think.

I'm still working automating the mileage multipliers to make the spreadsheet almost brainless. The formulas for the lookups are doing my head in, and thanks to El Al this is a little more complex than expected.

Some of the El Al fare buckets have different status multipliers for them. Most of the spreadsheet will handle this, just need to deal with the lookups to make this happen. I don't think too many potential users are El Al flyers so its not going to stop you from using the worksheet.

Thanks again to Serfty, medhed, drewbles, anat0l, jas, docjames, and yo yo mama who've all provided information, assistance and testing for this endeavour to date.

My milestone was to post this to FT once I hit V0.3, and while the automation for mileage multipliers based on fare buckets isn't fixed yet, I'm positing it anyway to give it some wider feedback and exposure.

 

[anat0l]

Once again thewinchester you continue to amaze.

Now for every programmer's dread: the bug report... is it any wonder that the testers are one of the most hated people in the software development cycle.

For reference, I selected a handful of flights from my last year of travel. Set opening status to Platinum and mode to Retain.

• In Retain mode, the status column in the calculation table reflects the status from Bronze as if you were qualifying for the status (albeit using the SC levels for the Retain mode). In actual fact, if you are retaining your status, your status used to calculate the status bonus should be the current status you're retaining, not the "progressively retained" status as the spreadsheet attempts to do.
  
  The easiest way to address this is to change the formula in the M column to "IF Mode = Qualify, use the Qualify status (not changed), else use the Opening Status (cell C4)". If you want to, then you can change the Status column (column AE) for Retain mode to something like "Yes" and "No" (i.e. you've retained the status in cell C4, or you haven't).
  
  This will necessarily require a bit of rework of the formula and maybe another hidden column (use the current column AE as the hidden column, then the new "Retain" column will compare the value in the AE column with the value in C4; if equal, status has been retained. Make sense?)
  
  If you're feeling brave, you can use conditional formatting to "hide" the text of the column (one of AD or AE) which doesn't match the selected mode.
• As a result of the status not holding for retain mode, the calculation of points has necessarily been off the mark as well.
• Status (bonus) points are based on the base points, not the mileage. (This is in case the base points are less than the mileage due to the multiplier).
• I see you've used a difference method for the MPG calculation rather than using a MAX statement. Fine, but the formula wasn't copied all the way down (which is why I found some odd calculations sticking here and there in the latter rows of the table).
• For the GCM link, you might like to append the following argument to the URL:
  &PATH-UNITS=mi
  The default units of GCM's output is nm, not mi.

The rest looks OK so far! Big hand to you again!

 

[thewinchester]

Debugging, the bane of the programmers existing. Heck, I've just finished writing some jQuery for the little website its on to get some statistics of how often its being viewed and downloaded.

Thanks again for the feedback anat0l, will add it to more of the bugfixing I'm doing on it this week.

 

[oz_mark]

Of course, GCM distances do not always equate to Qantas distances betwixt the same ports ...

Having had a look a few city pairs, it seems Qantas calculated the distances based on the Earth being a perfect sphere, while GCM models the earth being not a perfect sphere.

 

[medhead]

Great Effort. Thanks

I'd just say that I still think MPG difference is a complicated why to deal with the MPG. I've previously suggested a greater than test, but Anat0l's suggestion of the MAX function is probably more elegant.

Also as a longer term goal I'd suggest that the function of the qualify and retain columns should be combined. Once I retain I then what to qualify for the next level.

But it is great, sorry for banging on about this.

 

[anat0l]

If you want to be really cough, you should include the following disclaimers / assumptions:

• Loyalty bonuses will not be factored into total points
• If you have a lifetime status (e.g. LTS, LTG), it will also not be correctly factored into the qualification tracking or the status points.

I'm sure there are others (apart from the obvious GCM distance <> distance used by QFF); I'll find them as I go along.

On that note, another bug. The qualify tracking thing works fine if you start from Bronze. If you start from another status, then the status bonus and the qualify tracking thing doesn't work.

Of course this makes things quite complicated. For example, if you are a Gold to start your membership year, you'll start with 0 SC and need 600 SC to retain Gold or 1400 SC to qualify for Platinum. However, for the whole year (unless you qualify for Platinum), you'll enjoy the Gold 50% points loading. The calculator currently doesn't cater for this (it always assumes you start with Bronze).

 

[thewinchester]

For statuses, will work these into the system. Might just need another list for current status (current year or lifetime) to deal with arising status bonuses and lifetime levels.

I'm still working on the qualifying tracking, yet to perform silver, gold and platinum testing to work out the errors.

And yes, disclaimers would be a good idea.

If you want to be really cough, you should include the following disclaimers / assumptions:

• Loyalty bonuses will not be factored into total points
• If you have a lifetime status (e.g. LTS, LTG), it will also not be correctly factored into the qualification tracking or the status points.

I'm sure there are others (apart from the obvious GCM distance <> distance used by QFF); I'll find them as I go along.

On that note, another bug. The qualify tracking thing works fine if you start from Bronze. If you start from another status, then the status bonus and the qualify tracking thing doesn't work.

Of course this makes things quite complicated. For example, if you are a Gold to start your membership year, you'll start with 0 SC and need 600 SC to retain Gold or 1400 SC to qualify for Platinum. However, for the whole year (unless you qualify for Platinum), you'll enjoy the Gold 50% points loading. The calculator currently doesn't cater for this (it always assumes you start with Bronze).

 

[thewinchester]

Welcome to Version 0.4, a real milestone and incorporates more bug fixes based on feedback, as well as improving the operating mode and dealing with lifetime statuses.

I'm pretty sure I've captured all the bugs and feedback of anat0l and medhed to date (once again, greatly appreciated gents).

I'll say from the outset there's a truckload of hidden columns in this spreadsheet and a bucket load of extra worksheets with data for lookups. This is to firstly enable the system to handle additional airlines or changes in information, and also for multiple calculations and evaluations to deliver the right result. It may not be the cleanest solution for achieving it right now, but the main thing is that it works well, and works consistently.

Improvements specific to V0.4

• full summary of results, including points/sc's earned, lifetime loyalty bonuses earned, and value proposition summary now included at top of sheet in full.
• Status calculations now fixed completely, will factor all statuses correctly relevant to the operating mode and starting status, and accounts for starting with or earning lifetime status.
• Lifetime loyalty bonus calculations now factored into the spreadsheet.
• GCM link corrected to ensure miles and not nautical miles returned.
• Additional MPG formula column fixes completed, cabin and status bonus points now correctly calculated on base points and not sector mileage.

Features

Currently the spreadsheet will:

• Allow you to enter/select the airline, departure and arrival airport, cabin class and number of miles flown.
  • If number of miles is unknown, a single click will pass information on that sector thru to Great Circle Mapper to get the estimated mileage.
• Ensure you've entered in the valid arrival and departure airport codes
• Calculate miles based on the earning ratio you specify (this is soon to be automated to pre-fill with earning ratio for airline and cabin class specified)
  • If flying a carrier in a cabin class where SC's aren't earned, a zero value for all fields after earning ratio while still ensuring rest of spreadsheet calculations remain valid.
• Work out if a minimum points guarantee is acceptable to the carrier, and apply accordingly based on the airline and cabin class.
• Calculate if status credits, cabin bonuses and status bonuses are earned and apply accordingly.
• Provide a running total of miles and SC's earned.
• With sector cost information entered, provide some decision-making information

Feel free to download the spreadsheet (MS Excel, 471kb) and let me know what you think of the latest improvement set.

A sample spreadsheet tab is also included so you can get a better idea of how it works.

 

[medhead]

Just playing with it now, sorry to bear bad news, GCM is still returning nm.

Not a big problem as it's easy to change the units and get the mi answer.

 

[thewinchester]

Just playing with it now, sorry to bear bad news, GCM is still returning nm.

Not a big problem as it's easy to change the units and get the mi answer.

Bugger, thought I fixed that. Will investigate, along with some information supplied to me by an FT member who did something similar about 4 years ago - and managed to get all the point to point distances from somewhere.