Help please

I have a problem, which I have partly sorted, but cannot get any
further.

I need a function that will check the destination (table below,
although it doesn't view very well - sorry!), and then the length. So,
if the destination says Scotland, and it is less than 13861 mm in
length, the answer needs to be 0, if it is Scotland and the length is
between 13861 and 18288, the answer needs to be 80, and if it is
Scotland and the length is more than 18288, the answer is 160.

There are about 20 destinations and the destination can appear in any
position within the table, so I need a formula which will be able to do
this

I was able to do the first two successfully, but when I try to add the
third on I get an error.

The first two look like this

=IF(AND(A36="scotland",C36<13861),0,IF(AND(A36="sc otland",C3613861,C36<18289),80,IF(AND(A36="scotla nd",C3618289),160,IF(AND(A36="Cumbria",C36<13861) ,0,IF(AND(A36="Cumbria",C3613861,C36<18289),60,IF (AND(A36="Cumbria",C3618289),120))))))

Maybe it isn't possible, or I am doing it incorrectly. Any help would
be really appreciated

Thank you very much
Lynne


20% 40%
Destination Basic 13861mm to 18288mm 18289mm & over

Scotland 400 80 160
Cumbria 300 60 120
North-East 250 50 100
North-West 250 50 100
S.Yorks & Humber 150 30 60
E.Mids & Central 250 50 100
North Wales 300 60 120
East Anglia 350 70 140
W.Mids & Staffs 300 60 120
London & North 300 60 120
Ctrl London & South 350 70 140
Gloucs & S.Wales 350 70 140
South East & Coast 400 80 160
South West 400 80 160
W.Yorks & Lincs 200 40 80
Notts & Derbys 200 40 80


--
Lynneth
------------------------------------------------------------------------
Lynneth's Profile: http://www.excelforum.com/member.php...o&userid=26102
View this thread: http://www.excelforum.com/showthread...hreadid=508368

Assumptions:

C1:D1 contains 20% and 40%

A2:D2 contains Destination, Basic, '13861mm to 18288mm', and '18289mm &
over'

A4:D19 contains the data

F4 contains the 'Destination' of interest

G4 contains the 'Length' of interest

Formula:

=INDEX(C4:D19,MATCH(F4,A4:A19,0),MATCH(G4,{13861,1 8289}))

If, instead, you let C2:D2 contain 13861 and 18289, you could use the
following formula...

=INDEX(C4:D19,MATCH(F4,A4:A19,0),MATCH(G4,C2:D2))

The advantage being that you could easily change the parameters in C2
and D2 without having to change the formula.

Hope this helps!

In article ,
Lynneth wrote:

Help please

I have a problem, which I have partly sorted, but cannot get any
further.

I need a function that will check the destination (table below,
although it doesn't view very well - sorry!), and then the length. So,
if the destination says Scotland, and it is less than 13861 mm in
length, the answer needs to be 0, if it is Scotland and the length is
between 13861 and 18288, the answer needs to be 80, and if it is
Scotland and the length is more than 18288, the answer is 160.

There are about 20 destinations and the destination can appear in any
position within the table, so I need a formula which will be able to do
this

I was able to do the first two successfully, but when I try to add the
third on I get an error.

The first two look like this

=IF(AND(A36="scotland",C36<13861),0,IF(AND(A36="sc otland",C3613861,C36<18289)
,80,IF(AND(A36="scotland",C3618289),160,IF(AND(A3 6="Cumbria",C36<13861),0,IF(
AND(A36="Cumbria",C3613861,C36<18289),60,IF(AND(A 36="Cumbria",C3618289),120)
)))))

Maybe it isn't possible, or I am doing it incorrectly. Any help would
be really appreciated

Thank you very much
Lynne


20% 40%
Destination Basic 13861mm to 18288mm 18289mm & over

Scotland 400 80 160
Cumbria 300 60 120
North-East 250 50 100
North-West 250 50 100
S.Yorks & Humber 150 30 60
E.Mids & Central 250 50 100
North Wales 300 60 120
East Anglia 350 70 140
W.Mids & Staffs 300 60 120
London & North 300 60 120
Ctrl London & South 350 70 140
Gloucs & S.Wales 350 70 140
South East & Coast 400 80 160
South West 400 80 160
W.Yorks & Lincs 200 40 80
Notts & Derbys 200 40 80

Here's one play to try ..

See sample construct at:
http://cjoint.com/?cemNaolyr1
Reading a Table_Lynneth_wks.xls

Your source table is assumed in A2:D18

Enter the numbers: 13861, 18289 into C1:D1

Then we could put in say, D36, and copy down:
=IF(C36<13861,0,INDEX(OFFSET($A$3:$A$18,,MATCH(C36 ,$1:$1,1)-1),MATCH(A36,$A$
3:$A$18,0)))

This will return the required results

Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Lynneth" wrote in
message ...

Help please

I have a problem, which I have partly sorted, but cannot get any
further.

I need a function that will check the destination (table below,
although it doesn't view very well - sorry!), and then the length. So,
if the destination says Scotland, and it is less than 13861 mm in
length, the answer needs to be 0, if it is Scotland and the length is
between 13861 and 18288, the answer needs to be 80, and if it is
Scotland and the length is more than 18288, the answer is 160.

There are about 20 destinations and the destination can appear in any
position within the table, so I need a formula which will be able to do
this

I was able to do the first two successfully, but when I try to add the
third on I get an error.

The first two look like this


=IF(AND(A36="scotland",C36<13861),0,IF(AND(A36="sc otland",C3613861,C36<1828
9),80,IF(AND(A36="scotland",C3618289),160,IF(AND( A36="Cumbria",C36<13861),0
,IF(AND(A36="Cumbria",C3613861,C36<18289),60,IF(A ND(A36="Cumbria",C3618289
),120))))))

Maybe it isn't possible, or I am doing it incorrectly. Any help would
be really appreciated

Thank you very much
Lynne


20% 40%
Destination Basic 13861mm to 18288mm 18289mm & over

Scotland 400 80 160
Cumbria 300 60 120
North-East 250 50 100
North-West 250 50 100
S.Yorks & Humber 150 30 60
E.Mids & Central 250 50 100
North Wales 300 60 120
East Anglia 350 70 140
W.Mids & Staffs 300 60 120
London & North 300 60 120
Ctrl London & South 350 70 140
Gloucs & S.Wales 350 70 140
South East & Coast 400 80 160
South West 400 80 160
W.Yorks & Lincs 200 40 80
Notts & Derbys 200 40 80


--
Lynneth
------------------------------------------------------------------------
Lynneth's Profile:
http://www.excelforum.com/member.php...o&userid=26102
View this thread: http://www.excelforum.com/showthread...hreadid=508368