Formula calculations are incorrect

Hi James,

I assume that the value for "Break-Even Qty." is a calculation, correct?

Please change the format for "Break-Even Qty." so that you show 3-4 digits

Based on my calculation US$9,745,881.00 / US$97.00 would be 100,472.814 and not 100,473 as display.

Based on your selected format Numbers will show a value, but for the internal calculation the complete value will be used.

If you don't like this you would have to use ROUND to eliminate unwanted data.

Based on your region the , or the ; will be used to separate the different sections of a formula. If you write one thousand as 1,000.00 then the , is used as your formula separator. If you write one thousand as 1.000,00 then the ; is used as your formula separator.

Hope this will solve your question, please let me know if this worked for you or if something in unclear.

Ralf

Hi James,

Unfortunately there is no easy answer on when to use Round or how many decimals you should use.

Every case is different and sometimes you have to make a compromise.

Yes Excel has the identical functionality as Numbers. But there is one exception.

Excel has 3 round functions.

Numbers has also these 3 round functions, plus a special addition "MRound".

Here a comparison of the different functions.

Round, RoundUp & RoundDown was set to 0 decimals / MRound was set to 0.25

Unfortunately it is not easy to say when / how you should use round

I modified your example to show it. Average Cost was changed!

The exact number for Break-Even Qty. would be 100,512.4162

If you use normal round it would round down, because it ends in .4162

But 100,512 units would be not sufficient to reach Break-Even Qty., because the value must be above 100,512.4162

In this case you should use RoundUP, that will always give you the next full number.

You can include your calculation in the round functions, see last row of this table.

I always use 2 decimals for regular tables, if the unit is currency.

For the rest I pick something that shows what I need to know.

I only use round if I really have to, like if I have to create an expense sheet.

Here an example that shows that round can be tricky:

3 People will spent exact $100.00 in a restaurant (including tip), therefore each has to pay $33.33 (when you use round to 2 decimals)!

Unfortunately 3 times $33.33 is not $100.00 ;-(

Ralf

"Here an example that shows that round can be tricky:

3 People will spent exact $100.00 in a restaurant (including tip), therefore each has to pay $33.33 (when you use round to 2 decimals)!

Unfortunately 3 times $33.33 is not $100.00 ;-("

Just another example of where to use ROUNDUP. ;^)

Barry

What is the formula returning the "incorrect answer"?

What you have labeled the "correct answer" appears to ignore the "cost per unit" values and treat the selling price of each unit as the net profit from that sale.

Profit per unit, in its simplest form is the sale price - the cost price per unit.

Fixed costs are costs that remain the same regardless of the number of units produced or sold.

Breakeven occurs when the total costs are equal to the total income from sales.

Regards,

Barry