Round up or down for costs (invoice)

Question

Level 1

0 points

Round up or down for costs (invoice)

Hi I'm new with numbers but was good in excel. Now I try figure out how I can round costs in an invoice. For exampel:

In my row E10 I have the sum of a calculation let's say 1'200.- CHF. From this sum I need 8,33% which gives me in row E11 the sum of 99.96. Now I need to round this up or down. I wanted to add "round" to my formula in E11 which says =sum(E10)x8,33% but it won't work. I'm a little bit frustrated because with excel I was very fast and good but with numbers it seem's I'm to stupid to check the formulas *grrrr

Secondly I work with the german version not in english. I have to translate the formulas. Question: where (which row) do I have to round? E11 or already E10 and what exact formula is it to put in? Thank you for you help

iMac, OS X Mavericks (10.9.1)

Posted on Feb 26, 2014 6:54 AM

Reply

Answer 1

Badunit

Level 7

30,534 points

Feb 26, 2014 7:19 AM in response to angeli

The formula in Numbers is the same as Excel

=ROUND(E10*8,33%;2)

You do not need the SUM function in E11, you are not summing anything.

Reply

Answer 2

angeli Author

Level 1

0 points

Feb 26, 2014 7:35 AM in response to Badunit

Thx for your answer. Unfortunately it's not working your formula

This how is the formula when it calculates:

This when I try to add the "runden":

And this, when I follow your rules:

The only thing different is that you ad a " ; "

Help

I don't know what I'm doing wrong

Thx for the help

🙂

Reply

Answer 3

Feb 26, 2014 7:52 AM in response to angeli

What does the error message say when you click the triangle?

Reply

Answer 4

angeli Author

Level 1

0 points

Feb 26, 2014 8:05 AM in response to Wayne Contello

it say's it's a syntax error (well in german) 🙂

Reply

Answer 5

angeli Author

Level 1

0 points

Feb 26, 2014 8:17 AM in response to Wayne Contello

I just found out that when I try to make " * " that it appears automatically " + " and I cant change it into " * ".

The formula gives me the symbol automatically - I can't but a normal formula like in excel.

Reply

Answer 6

angeli Author

Level 1

0 points

Feb 26, 2014 8:23 AM in response to Wayne Contello

when I do it with the formula it appears like this:

he wants the number to be roundet (grey) and then the accurate decimal place - and then the formula E10x8,33%

the second thing is: normally in my formula it puts a " , " (comma) but you all put " ; ". so whats right now?

thank you I'm desperate - in excel I would have done it within seconds....

a

Reply

Answer 7

Yellowbox

Level 7

21,939 points

Feb 26, 2014 6:07 PM in response to angeli

Hi angeli,

I just found out that when I try to make " * " that it appears automatically " + " and I cant change it into " * ".

The formula gives me the symbol automatically - I can't but a normal formula like in excel.

Numbers 3 changes * to X in formulas. It is the multiplication operator.

Is this formula working as you want?

the second thing is: normally in my formula it puts a " , " (comma) but you all put " ; ". so whats right now?

This is specific to a region. If you use "." for the decimal point, use comma "," between arguments in a formula.

Regards,

Ian.

Reply

Answer 8

Mar 6, 2014 8:11 AM in response to Yellowbox

Dear Ian

Thx for your answer. I got it. I have "," between arguments in the formula. So far so good.

I still have the problem with the rounding. You asked if the formula is working - no!

I still have this invoice and I'm trying to round the amount up or down to the next whole 5 (currency). Means I need a round up or down after the decimal for exemple:

£ 34.56 -> must become £ 34.50

or

£ 34.58 -> must become £ 34.60

Help 🙂

Reply

Answer 9

Badunit

Level 7

30,534 points

Mar 6, 2014 8:20 AM in response to angelibox

Your first example, rounding 34.56 down to 34.50, is not in accordance with typical rounding conventions. Typically it would round up to 34.60. Is there some reason you need this strange way of rounding? If so, it can be done but we need to know the rules before we can suggest a formula.

I used a ";" between the arguments in the formula because you used "8,33%" in the multiplication. If a comma is your decimal separator, a semicolon is used to separate parameters.

Reply

Answer 10

Mar 6, 2014 9:11 AM in response to Badunit

This strange way of rounding is when we calculate with our Swiss money 🙂 means CHF 34.56 would actually give CHF 34.50 which is not strange but normal with our currency - the 5er step. The system must know when to round up or down to the next 5 as I mentioned earlier CHF 34.58 would be up CHF 34.57 would be down. I always need at the end a 5 or a 0 (currency). Can you help?

thx

Reply

Answer 11

SGIII

Level 8

37,550 points

Mar 6, 2014 10:53 AM in response to angelibox

Is this what you want?

=TRUNC(MROUND(A2,0.05),1)

This seems to produce the result you want. No negative currencies allowed!

SG

Reply

Answer 12

Badunit

Level 7

30,534 points

Mar 6, 2014 11:31 AM in response to angelibox

Just to be clear,

.01 through .07 get rounded down to .00

.08 through .09 get rounded up to .10

Is that correct? I don't know where "rounded up or down to the next 5" comes into this, though.

I'm not trying to be difficult but this is a new way of rounding for me and I am not clear on the rules.

Reply

Answer 13

Mar 6, 2014 11:56 AM in response to angelibox

Angel,

Here's my take:

The expression in column B is:

=ROUND(ROUND(2×B, 0)÷2,1) and the cell is set to display 2 places.

Jerry

Reply

Answer 14

SGIII

Level 8

37,550 points

Mar 6, 2014 12:37 PM in response to angelibox

A bit confusing! It appears that not all the statements about how Swiss currency quotations work can be true.

SG

Reply

Answer 15

Badunit

Level 7

30,534 points

Mar 6, 2014 2:02 PM in response to SGIII

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

Reply