Having a problem with calculations

[CaydBury]

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

[#2]

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

[#3]

@CaydBury 

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.

[#4]

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

[#5]

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.

[#6]

@CaydBury 

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)

[#7 · Accepted solution]

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! 

[#8]

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

[#9]

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.