+ Reply to Thread
Results 1 to 13 of 13

Calculating VAT (6%) and rounding up/down

  1. #1
    Registered User
    Join Date
    04-13-2011
    Location
    Swe
    MS-Off Ver
    Excel 2003
    Posts
    9

    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.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    04-13-2011
    Location
    Swe
    MS-Off Ver
    Excel 2003
    Posts
    9

    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
    Last edited by kalender; 11-19-2011 at 05:09 PM.

  3. #3
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    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.
    Life is about perseverance. Remember: today's mighty oak tree is just yesterday's nut that held its ground.

    If you like a post, please rate it with the scales icon (top right).
    If you feel really indebted, please consider a donation to charity, such as Feed the Children or Habitat for Humanity.

  4. #4
    Registered User
    Join Date
    04-13-2011
    Location
    Swe
    MS-Off Ver
    Excel 2003
    Posts
    9

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

    Quote Originally Posted by darkyam View Post
    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.


    Thanks in advance!!


    K

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    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.
    Entia non sunt multiplicanda sine necessitate

  6. #6
    Registered User
    Join Date
    04-13-2011
    Location
    Swe
    MS-Off Ver
    Excel 2003
    Posts
    9

    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?

  7. #7
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

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

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

  8. #8
    Registered User
    Join Date
    04-13-2011
    Location
    Swe
    MS-Off Ver
    Excel 2003
    Posts
    9

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

    Quote Originally Posted by darkyam View Post
    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?

  9. #9
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

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

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

  10. #10
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    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
    Please Login or Register  to view this content.

    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
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  11. #11
    Registered User
    Join Date
    04-13-2011
    Location
    Swe
    MS-Off Ver
    Excel 2003
    Posts
    9

    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.

    www.mediafire.com for upload or attach it in your post.


    THANKS!!
    Attached Files Attached Files

  12. #12
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    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.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    04-13-2011
    Location
    Swe
    MS-Off Ver
    Excel 2003
    Posts
    9

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

    Quote Originally Posted by vlady View Post
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1