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

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

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

"Emmaly" wrote:


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
.

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

--
Biff
Microsoft Excel MVP


"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

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

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

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



T. Valko;3654064 Wrote:
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

--
Biff
Microsoft Excel MVP




--
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 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???




--
Emmaly

Emmaly wrote:
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.

Try this...

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

--
Biff
Microsoft Excel MVP


"Emmaly" wrote in message
...

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???




--
Emmaly

You're welcome!

--
Biff
Microsoft Excel MVP


"Emmaly" wrote in message
...

Brilliant it works!!!!!

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




--
Emmaly