SOLVED

# Having a problem with calculations

Occasional Contributor

# Having a problem with calculations

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
8 Replies

# Re: Having a problem with calculations

I suspect that the values .35 and .28 are the result of a formula and the actual numbers are not exactly the values displayed.
Round the final calculation not the intermediate steps.

'---
Nothing Left to Lose

# Re: Having a problem with calculations

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.

# Re: Having a problem with calculations

@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!

# Re: Having a problem with calculations

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.

# Re: Having a problem with calculations

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)

best response confirmed by CaydBury (Occasional Contributor)
Solution

# Re: Having a problem with calculations

Ok 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!

# Re: Having a problem with calculations

Hey Nothing left to lose! Thanks for your response, sorry i didn't respond! Appreciate your help.

# Re: Having a problem with calculations

Hey mtarler! Thanks you, I think the confusing part for me was that the other calcs wer coming correctly (which I thought was much more complex). I am more use to pen paper and calculator lol anyway I appreciate your help for this.