Formula

[torquay]

Hi I am trying to use a formula that I am using in excel and cannot seem to translate it to access.
The excel formula is
=IF(A1<=20,10.45,IF(AND(A1>20,A1<=30),((A1-20)*0.35+10.45),(A1-30)*1+(10*0.35)+10.45))*1.255
Thank you Mr Excel for your help on that

I am trying to use it like this
Cost: IIf([CourierWeight]<=20,10.45,If(and([CourierWeight]>20,[CourierWeight]<=30),(([CourierWeight]-20)*0.35+10.45),([CourierWeight]-30)*1+(10.0.35)+10.45))*1.255
it wont let me use it as "if(and(" is incorrect.
What I am trying to work out is some shipping costs on weight
Example: Total package weight is 52.5kg
Pricing as follows
0-20kg = £10.45
20-30kg @ 0.35 per kg
40kg plus and additional £1 per kg

Thanks in advance

 

[CJ_London]

You are using if rather than iif - try:

Cost: IIf([CourierWeight]<=20,10.45,iif([CourierWeight]>20 AND [CourierWeight]<=30,([CourierWeight]-20)*0.35+10.45,([CourierWeight]-30)*(1+(10*0.35)+10.45))*1.255

Have correct for the right way to use AND is sql and corrected a typo

 

[torquay]

Thank you for your fast response. I have tried the corrected formula and it works great until the weight hits 32kg and then it doesn't calculate correctly
It should 31kg = £18.76
32kg = £20.02

The formula at the moment is correct at 31kg £18.76 but at
32kg it shows £37.52 which is not correct

Thanks

 

[Brianwarnock]

There should not be a ( before the 1 here

30)*1

Check all the bracketing

Brian

 

[torquay]

Thank you very much, the corrected formula is

Cost: IIf([CourierWeight]<=20,10.45,IIf([CourierWeight]>20 And [CourierWeight]<=30,([CourierWeight]-20)*0.35+10.45,([CourierWeight]-30)*1+(10*0.35)+10.45))*1.255

Works great