Calculating VAT (6%) and rounding up/down

1. Calculating VAT (6%) and rounding up/down

Hi,

Need some help guys. Don't know if this one will be a hard nut to crack but give it a try.

Check out the attached excel file. Instructions is in there!

THANKS IN ADVANCE. A big hug to the one that can solve this.

K

ps. I am using Excel 2007, if that matters.  Register To Reply

2. Re: Calculating VAT (6%) and rounding up/down

Hmm. Will probably need the function that round down the VAT. But both functions (if they are easy) wouldn't hurt   Register To Reply

3. Re: Calculating VAT (6%) and rounding up/down

I'm sorry, but I don't quite understand what you mean by choose the cell and how doing it manually is taking a long time. Is there a price somewhere that you need to lookup against the table?
If you want a simple rounding for each of these, that's very easy: =ROUNDDOWN(A2*0.06,2). You can, of course, replace ROUNDDOWN with ROUNDUP.  Register To Reply

4. Re: Calculating VAT (6%) and rounding up/down Originally Posted by darkyam I'm sorry, but I don't quite understand what you mean by choose the cell and how doing it manually is taking a long time. Is there a price somewhere that you need to lookup against the table?
If you want a simple rounding for each of these, that's very easy: =ROUNDDOWN(A2*0.06,2). You can, of course, replace ROUNDDOWN with ROUNDUP.
Sorry for being unclear.

By manually I mean... In order to do what I want (calculate the 6%, and round down), I need to select/change to the correct cell(number) everytime. For example:
=A01-(A01/1,06)
=A02-(A02/1,06)
=A03-(A03/1,06)

A01, A02, A03 etc. = The numbers that I am changing to the correct cell manually.

What I want is this to be automated, so it automatically chooses the cell on the same line, but in the previous column (A in this case).

The function have to do this automatically. So as you can see in the excel file:
Price column A:
A1 = 8,8
A2 = 15,2
A3 = 16,8
A4 = 43,2
A5 = 60,8

The VAT column - B, needs to have a function that select the cell on the same line/row in the PREVIOUS column, and do all the stuff that is necessary: /1.06, then round it down.

So for the VAT B Column, the results that I want is:
B1 = 0,49
B2 = 0,86
B3 = 0,95
B4 = 2,44
B5 = 3,44

But as I said: This needs to be automated. Im searching for a function that I can put in every B cell/the whole B column that does it all.

Once again:
x Automatically selects the cell (not specified before) on the same row, in the previous column (A).
x Does the math "( =A01-(A01/1,06)"
x Finally: Round down the sum from above ^

Haha tried to do my best with the exlpanation.. Hoping that someone in here can do this. Seems to be a hard one.

K  Register To Reply

5. Re: Calculating VAT (6%) and rounding up/down

That's exactly the way Excel copies formulas.

Select the cell with the formula, left-click the fill handle (the little square at the lower right of the selection marquee), and drag down.  Register To Reply

6. Re: Calculating VAT (6%) and rounding up/down

Haha oh totally forgot about that one, lol.

How do I combine a function (round down) then?  Register To Reply

7. Re: Calculating VAT (6%) and rounding up/down

Change =A01-(A01/1,06) to =Rounddown(A01-(A01/1,06),2)  Register To Reply

8. Re: Calculating VAT (6%) and rounding up/down Originally Posted by darkyam Change =A01-(A01/1,06) to =Rounddown(A01-(A01/1,06),2)
Thanks for you help. Unfortunely I didn't got it to work :< Got the standard error.

Hmmm. Does it work for you?  Register To Reply

9. Re: Calculating VAT (6%) and rounding up/down

Does your version use semi-colons instead of commas to separate arguments in a function?  Register To Reply

10. Re: Calculating VAT (6%) and rounding up/down

Hello kalender

If you've solve the problem in using comma and semi-colon issue.
think you should use only this will give you...
[/QUOTE]

45.564 because the last digit is 4 it will give you automatic 45.56
45.565 because the last digit is 5 it will give you automatic 45.57
[/QUOTE]

Round is the combination of rounddown and roundup  Register To Reply

11. Re: Calculating VAT (6%) and rounding up/down

God damn it. Can't get it to work at all.

"Does your version use semi-colons instead of commas to separate arguments in a function?"
When I tried to change the comma to a semi colon, I didnt got any error messages (popup/alert) except in the cell where it says "NAME?"

Hmm. Check out the attached file "Add the function to the VAT column.xlsx" @ this post. Would be awesome if someone of you guys could add the function. Cant get it to work here. Even changed to the swedish function for round down, which is "RUNDA.NER".

Installed a multilangual function add on so it should work anyway.

THANKS!!  Register To Reply

12. Re: Calculating VAT (6%) and rounding up/down

Hello
Same Problem with you.

Solution
Type first the formula A2-(A2/1.06) then enter
edit the formula put round.
Here's the sample of your file - edited.  Register To Reply

13. Re: Calculating VAT (6%) and rounding up/down Originally Posted by vlady Hello
Same Problem with you.

Solution
Type first the formula A2-(A2/1.06) then enter
edit the formula put round.
Here's the sample of your file - edited.
THANKS!! Working now Appreciate your and everyone elses help here.

Take care guys
/K  Register To Reply