Rate calculation (Excel 2003 SP3)

[steveh]

HI all

I am trying to calculate a tariff that has fixed rates from 0.5 to 99.5 kilos and is based on Country selection and then 10 zones, I can return the correct rate OK by using this formula

This formula is in B17 =INDEX($B$27:$K$225,MATCH(J10,$A$27:$A$225,0),H5) - B27:K225 are the values J10 is the chargeable weight and H5 is the zone, all well and good, I then have rates per kilos as follows

>99.5 <300 - A227 = weight B227:K227 = rate per kilo
>300 <500 - A228 = weight B228:K228 = rate per kilo
>500 <1000 - A229 = weight B229:K229 = rate per kilo
+1000 A230 = weight B230:K230 = rate per kilos

I thought that the best way foward was to have 5 cells to return the <0.99.5 rate, >99.5<300,>300<500,>500<1000,+1000 and then for the charge column I could use 5 nested IFs

As you can well imagine I am stuck (no, not that stuck who is an accomplished photographer, just a normal stuck)

For the .99.5,300 I started to construct a formula

IF(J10,>99.5<300,INDEX(B227:K227,MATCH(J10,A227:K227,0),H5 and that is where the brain has gone into meltdown, what I am tring to say is that if the wight is >99.5 but less than <300 the answer would be B17 + the rate per kilo according to the zone (H5) would be * by the differnence over 99.5 to 300 kilos and added to B17.

I hope that is clear

[HansV]

=IF(J10,>99.5<300,... makes no sense to Excel. You could use =IF(AND(J10>99,5,J10<300),...

There is probably a more efficient way to do what you want, but I'd have to see a copy of the workbook.

[Rudi]

Another little tip (esp. that your values are eliminated sequentially) is to structure the logical expression like this:
(Then you don't need to use the AND() to test between ranges.

=IF(J10>1000,...,IF(J10>500,...IF(J10>300,...,IF(J10>99.5,... , ...))))

[steveh]

Hi huys

Please attached zipped version of what I am trying to do

Thanks

Editted: I think I may have got there on my own combining formulas etc. supplied in the past both here and on Woody's but I would appreciate it if somebody could take a look and check that my logic is sound

[Rudi]

It looks good to me.
Since you evaluate only one row there is no need to use if conditions to check the range between...