IF CONDITION

[iyervsv]

I have to calculate rate of duty from 1.4.2005 to 29.02.2008 @ 16%

From 1.3.2008 to 6.12.2008 the same is to be @ 14% and from 7.12.2008 to 23.02.2008 it has to calculated @ 10% and from 24.02.2008 onwards it should be @ 8%


I have Invoice Date in A2 and I have Gross Value in d2 and I want the formula to calculate the duty payable in E2..


The date column is in MM/DD/YYYY format... whether I can change the format to DD/MM/YYYY.


Thanks in advance

 

[Hui]

Iyervsv

I have assumed you have a table of Dates and Rates in H1:I4

Adjust range to suit

I have used 2 named ranges

A2: IDate

D2: Gross

=IF(AND(IDate<$H$2,IDate>=$H$1),YEARFRAC(IDate,$H$2,1)*Gross*$I$1,0)+IF(IDate<$H$3,YEARFRAC(MAX(IDate,$H$2),$H$3,1)*Gross*$I$2,0)+IF(IDate<$H$4,YEARFRAC(MAX(IDate,$H$3),$H$4,1)*Gross*$I$3,0)+IF(IDate<NOW(),YEARFRAC(MAX(IDate,$H$4),NOW(),1)*Gross*$I$4,0)

You may want to check this!

 

[iyervsv]

Dear Mr Hui


This is too much of a stuff for me. First let me try to understand the formula you've written.


my data is like this...A2 is Billing Date B2 is Billing Quantity, C2 is Price per unit and D2 is Gross Value (b2*c2 = d2) Now I want to calculate tax payable on d2


BILLING_DATE BILLING_QUANTITY PRICE GROSS_VALUE

01/04/2007 275 25.61 7043

02/04/2007 150 45.92 6888

03/05/2007 2200 6.90 15180

03/05/2007 3000 15.99 47970

03/05/2007 2200 10.53 23166

03/05/2007 5500 10.53 57915

03/05/2007 1350 21.06 28431

03/05/2007 6000 12.75 76500

03/05/2007 1800 12.75 22950

03/05/2007 275 6.23 1713

03/05/2007 100 100.15 10015

03/05/2007 550 20.45 11248

03/05/2007 150 10.54 1581

03/05/2007 600 6.53 3918

03/05/2007 3300 6.90 22770

03/05/2007 1100 4.89 5379

03/05/2007 1800 12.75 22950

03/05/2007 600 12.75 7650

03/05/2007 1875 4.02 7538

03/05/2007 450 54.02 24309

12/12/2007 1100 14.27 15697

12/12/2007 2100 15.99 33579

12/12/2007 1100 10.53 11583

12/12/2007 7500 4.39 32925

12/12/2007 5500 8.85 48675

12/12/2007 1100 7.92 8712

12/12/2007 1100 7.92 8712

12/12/2007 600 17.55 10530

12/12/2007 2750 4.89 13448

12/12/2007 600 14.77 8862

12/12/2007 1800 14.77 26586


I have to calculate rate of duty according to A2 i.e. from 1.4.2005 to 29.02.2008 @ 16%

From 1.3.2008 to 6.12.2008 the same is to be @ 14% and from 7.12.2008 to 23.02.2008 it has to calculated @ 10% and from 24.02.2008 onwards it should be @ 8%


Thanking you in advance


V S Venkatraman

 

[Hui]

I have assumed you have a table of Dates and Rates in H1:I4

[pre]

________H		I
1	1/04/2005	14%
2	1/03/2008	12%
3	7/12/2008	10%
4	24/02/2009	8%

[/pre]

So tax rate E3: =VLOOKUP(A2,$H$1:$I$4,2)

So Tax Payable will be =D2*VLOOKUP(A2,$H$1:$I$4,2)

 

[niting]

Hui,


What is the YearFrac formula achieving in the 1st reply to this post???


Thanks

Niting

 

[Hui]

I thought Iyervsv was after the combined fees payable since the nominated date for various rates along the way

Yearfrac returns the fraction of a year between two dates and hence the total amount payable between the 2 dates is Yearfrac x Gross x Rate

Which was then added together