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(M2>501,M2<=2000),M2*'UK rate 2010'!C$9/1000,IF(AND(M2>2001,M2<=3000),M2*'UK rate 2010'!E$9/1000,IF(AND(M2>3001,M2<=4000),M2*'UK rate 2010'!F$9/1000,IF(AND(M2>4001,M2<=5000),M2*'UK rate 2010'!G$9/1000,IF(AND(M2>5001,M2<=7500),M2*'UK rate 2010'!H$9/1000,IF(AND(M2>7501,M2<=10000),M2*'UK rate 2010'!I$9/1000,IF(AND(M2>10001,M2<=12500),M2*'UK rate 2010'!J$9/1000,IF(AND(M2>12501,M2<=15000),M2*'UK rate 2010'!K$9/1000,IF(M2>15001,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
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(M2>501,M2<=2000),M2*'UK rate 2010'!C$9/1000,IF(AND(M2>2001,M2<=3000),M2*'UK rate 2010'!E$9/1000,IF(AND(M2>3001,M2<=4000),M2*'UK rate 2010'!F$9/1000,IF(AND(M2>4001,M2<=5000),M2*'UK rate 2010'!G$9/1000,IF(AND(M2>5001,M2<=7500),M2*'UK rate 2010'!H$9/1000,IF(AND(M2>7501,M2<=10000),M2*'UK rate 2010'!I$9/1000,IF(AND(M2>10001,M2<=12500),M2*'UK rate 2010'!J$9/1000,IF(AND(M2>12501,M2<=15000),M2*'UK rate 2010'!K$9/1000,IF(M2>15001,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