First, we can simplify the formula by moving the repeated math operations
outside the IF function. Next, your if statements read like a table, so lets
use a lookup function. Let's assume you just put the zone number into C2,
not the text string "Zone 1" (or you could use a formula to strip out the
number...)

=OFFSET('UK rate
2010'!$B$8,C2,LOOKUP(M2,{0,501,2001,3001,4001,5001 ,7501,10001,12501,15001},{0,1,2,3,4,5,6,7,8,9,10}) *M2/1000

You might also want to take a look at the HLOOKUP function. Depending on
your data setup, this might be a 1-shot formula you can use.

--
Best Regards,

Luke M
"Emmaly" wrote in message
...

Dear All,
I am writing a massive IF statement and i have nearly got it to work but
i need help with the last bit.
I have a got a price list for shipping with weight brackets and
different prices, so far i have written the IF so that it looks to see
how many kilos i am shipping and then it looks on the price list and
then calculates the correct shipping cost.
So far this works.. see IF below

=IF(M2<=500,'UK rate 2010'!B$9*M2/1000,IF(AND(M2501,M2<=2000),M2*'UK
rate 2010'!C$9/1000,IF(AND(M22001,M2<=3000),M2*'UK rate
2010'!E$9/1000,IF(AND(M23001,M2<=4000),M2*'UK rate
2010'!F$9/1000,IF(AND(M24001,M2<=5000),M2*'UK rate
2010'!G$9/1000,IF(AND(M25001,M2<=7500),M2*'UK rate
2010'!H$9/1000,IF(AND(M27501,M2<=10000),M2*'UK rate
2010'!I$9/1000,IF(AND(M210001,M2<=12500),M2*'UK rate
2010'!J$9/1000,IF(AND(M212501,M2<=15000),M2*'UK rate
2010'!K$9/1000,IF(M215001,M2*'UK rate 2010'!L$9/1000,))))))))))

But the problem is that there are 8 different shipping zones, so i need
it to say if cell C2 says zone 1 look at the prices in row 9 on the 'UK
rate 2010' sheet if C2 says zone 2 look at row 10 etc etc which i don't
think it can do.

I was wondering if i can copy the above formula to somewhere else in the
sheet (cell C100 for example) and do an IF that says if C2 = zone 1
insert formula in cell C100 If it says zone 2 insert formula in C101 and
i do an IF for each shipping zone. The thing is when i tried this
because the formula was looking at cell M2 it only ever looked at that
cell i couldn't get it to understand that the M is fixed but it needs to
replace the row number to which ever number it has inserted to.

Sorry this is such a rambling question but i feel like i am so close i
just can't quite work out the last bit!!

Please help!!! Thank you Emmaly




--
Emmaly