# IF formular with multiple rules

1. ## IF formular with multiple rules

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. I think the problem is I dont know how to write the "but" correctly

Thanks a lot.

2. ## Re: IF formular with multiple rules

``Please Login or Register  to view this content.``

3. ## Re: IF formular with multiple rules

Try

=B1*IF(A1<--"2008-12-01",17.5%,IF(A1<--"2010-01-01",15%,IF(A1<--"2010-04-01",17.5%,20%)))

4. ## Re: IF formular with multiple rules

Simplest way is to work from largest to smallest. Then you don't need to use any AND statements (i.e. AND(A1> 1/12/08, A1< 1/10/10))

=IF(A1>DATEVALUE("4/1/2011"),B1*0.2,IF(A1>DATEVALUE("1/1/2010"),B1*0.175,IF(A1>DATEVALUE("1/12/2008"),B1*0.015,B1*0.175)))
Does that work for you?

5. ## Re: IF formular with multiple rules

thanks for that guys.

The only one that worked when I copied and pasted it directly into excel was tlafferty's (maybe because it was written in a different format??).

Anyway, that's good enough for me.

It's going to save me and my colleauges a lot of time, and prevent the wrong VAT rate being applied on invoices.

Thank you again.

6. ## Re: IF formular with multiple rules

How to mark a thread Solved
Go to the first post
Click edit
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Also, next time you crosspost, please make sure you provide links to any and all posts in other forums.

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

#### 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