Calculating Costs when Rounding

[Dexter2]

An automated system is used for costs. Attached you will see two examples and a formula. When calculating manually the results are always one cent less. When inquired, was told it has to do with the system rounding. Unsure how that could be, have any of you experienced similar when calculating costs in Excel?

[TMS]

Begs the question: how do you calculate the result manually?

It may well be a result of floating point errors.

I can't help thinking that all the intermediate rounding does you no favours. That said, removing them all doesn't seem to make much difference to the end results.

Why, for example, do you have ROUND(Rate,6) when is 0.055?

Maybe use ROUNDUP and/or ROUNDDOWN rather than just ROUND.

[Eeza Goodun]

I think your problem is your "automated system" rounds your fortnightly amount (14/365) to 6 places, whereas when you put it into your calculator, I'm tipping you just multiply by 14 and divide by 365.

The two respective amounts (to 3 decimal places, which is probably how your 'manual system' works) are 1289.179 (rounded to 2 decimal places is 1289.18) and 1289.185 (Rounded to 2 decimal places is 1289.19), so there is your 1 cent difference.

I think your automated system is correct.

[Dexter2]

Thank you.

There honestly is no reason. The formula to round to 6, is technically only provided and applied to the top cell. I have since removed it from the 0.550 section.

[Dexter2]

I think your problem is your "automated system" rounds your fortnightly amount (14/365) to 6 places, whereas when you put it into your calculator, I'm tipping you just multiply by 14 and divide by 365.

The two respective amounts (to 3 decimal places, which is probably how your 'manual system' works) are 1289.179 (rounded to 2 decimal places is 1289.18) and 1289.185 (Rounded to 2 decimal places is 1289.19), so there is your 1 cent difference.

I think your automated system is correct.

Just now seen this, thank you as well. Would that mean to break the formula further to calculate it separately?

[Eeza Goodun]

Would that mean to break the formula further to calculate it separately?

I'm not sure what you are asking here. I'm no accountant but the way I would do things is to calculate each amount on the sheet separately, round each value to 2 places, then add them up, which is effectively what you are doing. That is, your excel calculation is correct.

I think the 'problem' has occurred because when doing things 'manually' you have calculated to 3 places (giving you half a cent) and then rounded THAT to the nearest cent, which gives you that extra cent.

35177*14/365-60.07 = 1289.18479452 If you round THAT to 2 places you get 1289.18 (Correct)

Whereas;

35177*14/365-60.07 = 1289.185 when rounded to the nearest half a cent. So when you round THAT to the nearest cent you get 1289.19 (which is 1 cent too much).

Your leap year calculation is 'incorrect' in your sheet because you have rounded 14/366 to 6 places, which is enough to make the one cent difference. (Although this 1 cent difference is 'lost' in the .055 multiplication, so I'm not sure how you got that extra cent in your manual calculation).

Manual; 34152*14/366 - 130.63 = 1175.73 (rounded to nearest cent)
Sheet: 34152* .038251 - 130.63 = 1175.72 (rounded to nearest cent)

[Dexter2]

The extra cent is in the automated system. I am trying to have this reflected in the spreadsheet.

Personally, I thought the Excel result is correct. The discounts and fee are all separate section calculations.

Below is an explanation to determine the Cost cell:

The following prescribed methods and formulas must be used in calculating. In all instances, mills must be dropped after each separate calculation.

365 Day Year = 14/365 = .038356
Biweekly Rate = .038356 x Total Annual
Day Rate= .10 x biweekly rate

[Eeza Goodun]

Day Rate= .10 x biweekly rate

Where is this calculation in your sheet?

[Dexter2]

In the Cost cell.

[Eeza Goodun]

Not in the sheet you put up. The cost is a typed in number. What CELL REFERENCE is that calculation in?

[Dexter2]

Within cells H6 and H16. The rounding to 6 was based on my possible misunderstanding on “rounding to mili” for the (14/365).

[Eeza Goodun]

I think we are at cross purposes here; EXACTLY where is this multiplication in your sheet. .10 x biweekly rate

I don't want to know where it is thought about or where you have used it in a calculation and THEN put a resultant number it in your sheet, I want to know where the figure 0.10 us actually in your sheet.

[Dexter2]

It is not used for this specific sheet. It’s only shown for transparency.

[Eeza Goodun]

In that case I'd say your automated system is wrong and trying to get your excel sheet to match up with something that is wrong is pretty futile.

[Dexter2]

Agreed, though I’m not in a capacity to challenge that.