Round up or down for costs (invoice)

If these were dollars, I'd just say we were rounding to the nearest half dollar.

Jerry

If these were dollars, I'd just say we were rounding to the nearest half dollar.

Maybe, but:

"£ 34.56 -> must become £ 34.50

or

£ 34.58 -> must become £ 34.60"

And a nice link to add to the confusion.

SG

I was referring to the link in Badunit's post. Sorry I wasn't clear.

Jerry

Jerry,

I know you we're referring to the prior post, not to the one I quoted. What's interesting is that this thread isn't the only place where there's (legitimate) confusion about this.

SG

Hello

If I understand it correctly, you may use MROUND() (German VRUNDEN()) to get what you want.

In German, decimal-comma:

E10    1200
E11    =VRUNDEN(E10 * 8,33%; 0,05)

In Engilsh, decimal-period:

E10    1200
E11    =MROUND(E10 * 8.33%, 0.05)

Note that MROUND(X, 0.05) will round X as follows, for example:

X     => MROUND(X, 0.05)
1.020 => 1.00
1.021 => 1.00
1.022 => 1.00
1.023 => 1.00
1.024 => 1.00
1.025 => 1.05    * rounded up
1.026 => 1.05
1.027 => 1.05
1.028 => 1.05
1.029 => 1.05
1.030 => 1.05

So if you want to round 1.02* to 1.00, you'd need to pre-process X as follows:

X     => MROUND(ROUNDDOWN(X, 2), 0.05)
1.020 => 1.00
1.021 => 1.00
1.022 => 1.00
1.023 => 1.00
1.024 => 1.00
1.025 => 1.00
1.026 => 1.00
1.027 => 1.00
1.028 => 1.00
1.029 => 1.00
1.030 => 1.05    * rounded up

That is, in your example,

(German)

E10    1200
E11    =VRUNDEN(ABRUNDEN(E10 * 8,33%; 2); 0,05)

(English)

E10    1200
E11    =MROUND(ROUNDDOWN(E10 * 8.33%, 2), 0.05)

Regards,

H

Of the three main approaches suggested, two with MROUND and one with ROUND, Hiroto's version of the MROUND approach (column E) appears most likely to be doing the right thing:

"£ 34.56 -> must become £ 34.50" must have been an unintentional red herring.

SG

Badunit wrote:

My searches for "swiss rounding" bring me to "swedish rounding". I wonder if one of the systems on this wikipedia page is it and, if so, which one it is. None of them seem to fit.

http://en.wikipedia.org/wiki/Swedish_rounding

I found the following description on the website for QAD, a global Enterprise Software vendor...

For orders inside of Switzerland in Swiss Francs (CHF), the total amount of an invoice must be rounded to a multiple of 5 Rappen, or 0.05 CHF. The detailed rounding specification is as follows:

>0.00 and <0.025 must be rounded down to x.x0

>=0.025 and <0.05 must be rounded up to x.x5

>0.05 and <0.075 must be rounded down to x.x5

>=0.075 and <0.10 must be rounded up to x.x0

There may be different requirements for parts of the invoice other than the "total amount". I found another reference that specified additional digits after the decimal. I think the solution is very context sensitive.

Jerry

"For orders inside of Switzerland in Swiss Francs (CHF), the total amount of an invoice must be rounded to a multiple of 5 Rappen, or 0.05 CHF. The detailed rounding specification is as follows:

>0.00 and <0.025 must be rounded down to x.x0

>=0.025 and <0.05 must be rounded up to x.x5

>0.05 and <0.075 must be rounded down to x.x5

>=0.075 and <0.10 must be rounded up to x.x0"

That description is essentially identical to what's used for retail cash sales in Canada since the RCM discontinued use of the penny ($0.01 coin) a couple of years ago.

Prices are stil posted to the nearest cent, and credit or debit sales are recorded as that amount, but cash sales are rounded to the nearest nickel ($0.05), following the same pattern set out above.

Thanks, Hiroto, for pointing out MROUND / VRUNDEN. Much cleaner than the gymnastics needed using ROUND / RUNDEN!

Regards,

Barry

MROUND / VRUNDEN. Much cleaner than the gymnastics needed using ROUND / RUNDEN!

The MROUND I suggested upthread previous page was a "different" MROUND, I guess.🙂 Very interesting function.

SG

@SG,

I've been away, and just started reading this discussion again today. I recognized the first part, and must have skipped to (near) the end and missed your post.

Thanks, and apologies!

Regards,

Barry

Hi Barry,

This thread is getting a little long, so it's undoubtedly a case of tl;dr. 🙂

Anyway learned quite a bit: Swiss rounding, Swedish rounding, Canadian rounding, and Jerry's phrase that I am going add to my arsenal for discussing the myriad calculation and quoting oddities one encounters in finance: "the solution is very context sensitive." Can say that again!

SG

Hi SG,

"This thread is getting a little long, so it's undoubtedly a case of tl;dr. 🙂"

More a case of (my) short term memory failure—Your post (with MROUND) was right there on the first page! I even recall reading the remark about 'no negative currencies' the first time through.

In any case, I think a method of obtaining 'correct' results has been reached. Now if Angeli would return and let us know which method works (and what the correct results should be for each od a set of 11 values between 11.00 CHF and 11.10 CHF, we could all say 'Ahhh! So that's it,' and go spend some Swiss Francs (or other currency). 😝

Regards,

Barry

Dear all

I'm sorry for answering late but I was unable to search for solutions when I'm quicker by doing it shortly from hand 🙂 Now I'm back reading all the posts and would like to thank Hiroto from Japan. That was the solution for my problem:

E11    =VRUNDEN(ABRUNDEN(E10 * 8,33%; 2); 0,05)

although correctly in German it's =VRUNDEN(E10 * 8,33%, 0.05) I only needed comma after 8,33% and 0.05 (point)

I couldn't find a solution for rounding down (because this here is up)?

SGIII:

When I have 34.56 -> must become 34.55

When I have 34.58 -> must become 34.60

I need to round to the next decimal of 5!

I haven't found the solution to put the right formula when I have the invoice as follow:

E10 Total = 1'100.-

E11 8,33% = 91.63

E12 Total = 1'191.63

Do I need a extra row to put the formula in E11? Or a 4th row to put the EndTotal of 1'191.65? I can't just put it in E11 nor E12. Won't accept the formula as I mentioned it above.

At the end I must say: I'm quicker doing it by hand or using Excel! Takes me 3 Seconds instead of finding a solutions over weeks 😟

Would like to use Numbers more frequently but not with problems like these. That was a small one! The other big Problem I have is not even close to a solution!

But thank all of you for your kind help. Much appreciated. Thx!!

Angeli

Just to let you know how my invoice looks like now - still not 100% happy but anyway so far it works:

Glad to read you've found something that works for you, which I assume uses MROUND with Hiroto's version of "pre-processing." I'm curious what solution you have in Excel that is easier than that.

SG