PDA

View Full Version : Basic if question



heyzeus
2011-09-27, 06:56 PM
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.

Ken Puls
2011-09-27, 09:57 PM
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.

Bob Phillips
2011-09-27, 11:56 PM
Yet another cross-posting b#m - ExcelForum http://www.excelforum.com/excel-general/794096-if-formular-with-multiple-rules.html, where he acknowledges he has an answer.

Ken Puls
2011-09-28, 05:22 PM
Based on the timestamps, he posted there after I gave the solution above. Nice.