Help with a really big IF statement

[Emmaly]

Dear All,
I am writing a massive IF statement and i have nearly got it to work bu
i need help with the last bit.
I have a got a price list for shipping with weight brackets an
different prices, so far i have written the IF so that it looks to se
how many kilos i am shipping and then it looks on the price list an
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*'U
rate 2010'!C$9/1000,IF(AND(M2>2001,M2<=3000),M2*'UK rat
2010'!E$9/1000,IF(AND(M2>3001,M2<=4000),M2*'UK rat
2010'!F$9/1000,IF(AND(M2>4001,M2<=5000),M2*'UK rat
2010'!G$9/1000,IF(AND(M2>5001,M2<=7500),M2*'UK rat
2010'!H$9/1000,IF(AND(M2>7501,M2<=10000),M2*'UK rat
2010'!I$9/1000,IF(AND(M2>10001,M2<=12500),M2*'UK rat
2010'!J$9/1000,IF(AND(M2>12501,M2<=15000),M2*'UK rat
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 nee
it to say if cell C2 says zone 1 look at the prices in row 9 on the 'U
rate 2010' sheet if C2 says zone 2 look at row 10 etc etc which i don'
think it can do.

I was wondering if i can copy the above formula to somewhere else in th
sheet (cell C100 for example) and do an IF that says if C2 = zone
insert formula in cell C100 If it says zone 2 insert formula in C101 an
i do an IF for each shipping zone. The thing is when i tried thi
because the formula was looking at cell M2 it only ever looked at tha
cell i couldn't get it to understand that the M is fixed but it needs t
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
just can't quite work out the last bit!!

Please help!!! Thank you Emmal

 

[Luke M]

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.

 

[Eduardo]

Hi,
could you post an example of your data and table it will help

 

[T. Valko]

Create a table where the row headers are the weight intervals and the column
headers are the zones (or vice versa). Then, you'd look for the intersection
of the weight and zone to get the price.

Here's a small file that demonstrates this.

2D lookup.xls 16kb

http://cjoint.com/?cxt7775H1q

One thing I noticed in your formula is that you're excluding several
possible weights:

<=500...>501...that excludes 501
<=2000...>2001...that excludes 2001
<=3000...>3001...that excludes 3001
etc
etc

 

[Emmaly]

Hi thanks for this Weirdly on my excel sheet it includes <= on th
501, 2001 but when i have copied and pasted the formula onto this pag
it hasn't picked them up.

Right i am goign to put all the shipping info into a table as shown an
see how it goes.

I think if this works we deserve a medal!!!

 

[Emmaly]

Brilliant it works!!!!!

You are AMAZING, THANK YOU!!!!!!!!!

 

[Emmaly]

Oh dear next problem!!!

How do i get get a formula to recognise an #N/A??

Basically i want to say if the cell says #N/A don't include it, but i
it has a number in it then add in the number.

I tried the following but it didn't work (T2 being the cell with it in)

=IF(T2="#N/A",R2+S2,R2+S2+T2)

Any ideas anyone??

 

[Glenn]

Oh dear next problem!!!

How do i get get a formula to recognise an #N/A??

Basically i want to say if the cell says #N/A don't include it, but if
it has a number in it then add in the number.

I tried the following but it didn't work (T2 being the cell with it in)

=IF(T2="#N/A",R2+S2,R2+S2+T2)

Any ideas anyone???

=IF(ISNA(T2),...

Look for "IS Functions" in the help file.

 

[T. Valko]

Try this...

=SUMIF(R2:T2,"<1E100")

 

[T. Valko]

You're welcome!