Oct 12 2022 10:40 AM - edited Oct 12 2022 12:04 PM
Hello All,
Having trouble with a formula for a basic calculation. Currently I am trying to calculate a fare based upon the trip in miles.
So I have the formula:
=(distance in miles) x (available seats) or =162 x .35 which would result in $56.70, however it results in 55.97 and the formula regardless of the distance for example even if the cell numbers are 199 x .28 which would equal $55.72 it is giving me a result of 55.97
Better Example of the Problem (I cannot upload screen shots)
Nautical Miles | # of Seats on aircraft | Available Seat Mile | Direct Operating Costs (DOC) | Fixed Costs | CASM |
| Fare |
400 | 9 | 3600 =[@[Nautical Miles]]*[@['# of Seats on aircraft]] | 404.58 | 99.12 |
.14
(=([@[Fixed Costs]]+[@[Direct Operating Costs (DOC)]])/[@[Available Seat Mile]]
| incorrect | 55.97
=[@[Nautical Miles]]*[@CASM] |
400 | 9 | 3600 =[@[Nautical Miles]]*[@['# of Seats on aircraft]] | 404.58 | 99.12 |
.14 (=([@[Fixed Costs]]+[@[Direct Operating Costs (DOC)]])/[@[Available Seat Mile]] | correct | 56.00
=[@[Nautical Miles]]*.14 (or the total of CASM) |
363 | 9 | 3267 | 404.58 | 99.12 | .15 | incorrect | 55.97 |
363 | 9 | 3267 | 404.58 | 99.12 | .15 | correct | 58.08 |
Oct 12 2022 11:58 AM
Oct 12 2022 12:16 PM
In the first data row of your example, CASM isn't exactly 0.14, but 0.13991666666...
This causes the discrepancy.
By the way, your calculation divides by Nautical Miles, then multiplies with Nautical Miles again. That's superfluous. Fare is simply Total Cost/Number of Seats.
Oct 12 2022 02:08 PM
@Hans Vogelaar Hi I know that isn't exactly .14 but we are dealing with currency as CASM is expressed in cents (rounded to the nearest cent).
Sorry, I don't understand what you mean by nautical miles are divided and then multipied? Nautical miles are first multiplied to get the ASM (available seat mile)
ASM = Total number of seats on aircraft x Distance of trip (NM)
3600 = 9 x 400
Then to get CASM
CASM = Direct Operating Cost + Fixed Costs / ASM
$ .14 (.1399....)= (404.58+99.12)/3600
Nautical Miles are then multiplied to get the fare cost.
FARE = CASM * NM
56.00 = .14*400
I'm sorry sort of new at doing more indepth formulas like this. Thanks for your response!
Oct 12 2022 02:29 PM - edited Oct 12 2022 02:36 PM
What Hans is saying is that
FARE = (Direct Operating Cost + Fixed Costs ) / # seats
and then optional you could then
CASM = FARE / Distance of trip
in both response the point is that you have round off error. You MUST consider round-off error any time you round anything. Simple example:
20 / 3 * 3 = 20 right?
but do it in 2 steps and round to the nearest penny:
20/3 = 6.67
now
6.67 * 3 = 20.01
So it best practice to not round off until you get a final answer and by "final answer" that could be intermediate points depending on your sheet. If you calculate how much you pay your employees and then sum those values for total expenses you NEED to round off that pay amount because the check you cut will round it off and THEN you sum. But in other cases you must carry the fraction all the way through and ideally minimize any redundant calculations to prevent round-off errors. The point is, if you want a value to be rounded then round it and only use that rounded value then on. Don't compare calculations done with rounding to calculations without rounding.
Oct 12 2022 02:32 PM
FARE = CASM * NM = TotalCost / ASM * NM = TotalCost / (Seats * NM) * NM = TotalCost / Seats
If you want CASM to be rounded to cents, change its formula to
=ROUND(([@[Fixed Costs]]+[@[Direct Operating Costs (DOC)]])/[@[Available Seat Mile]],2)
Oct 12 2022 03:12 PM - edited Oct 12 2022 03:19 PM
SolutionOk I get it now! I appreciate the help very much Hans! Thank you I was getting confused about your response as well as others saying to round it off, as you have may already have seen in my other responses I thought that the other calculations would have been way more complex then the final fare, and I guess that is the point for this practice with excel...I will do other examples to make sure i have it down. Once again thanks!
Oct 12 2022 03:13 PM
Oct 12 2022 03:17 PM