Results 1 to 4 of 4

Thread: Basic if question

  1. #1

    Basic if question



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi guys,

    This is a pretty simple formula question, but I can't find the answer anywhere (im probably not searching for the correct key words).

    Im trying to write a formular to work out VAT rates.

    Say A1=Date, B1=Amount, and C1=VAT rate. The formular will be written into C1, and needs to apply the following rules;
    If A1<01/12/08,B1*0.175

    If A1>01/12/08, but<01/10/10, B1*0.15

    If A1>01/01/10, but <04/01/10, B1*0.175

    If A1> 04/01/11, B1*0.20

    I hope this makes sense. I have tried linking the various rules a number of ways but can't find the soloution. It allways seems to give cummaltive VAT rate (so something like .17.5 + .15 say or gives a 0).

    Thanks a lot.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,027
    Articles
    79
    Blog Entries
    14
    Hi there, and welcome to the forum.

    This is actually a perfect place to use a VLOOKUP with an approximate match. (A lot easier to maintain than a really long IF formula.)

    Have a look in the attached file. Change your date in the grey cell and the VAT rate in F4 should update for you.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Yet another cross-posting b#m - ExcelForum http://www.excelforum.com/excel-gene...ple-rules.html, where he acknowledges he has an answer.

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,027
    Articles
    79
    Blog Entries
    14
    Based on the timestamps, he posted there after I gave the solution above. Nice.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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