'if' and 'and'

[Lynneth]

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="scotland",C36>13861,C36<18289),80,IF(AND(A36="scotland",C36>18289),160,IF(AND(A36="Cumbria",C36<13861),0,IF(AND(A36="Cumbria",C36>13861,C36<18289),60,IF(AND(A36="Cumbria",C36>18289),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

 

[Domenic]

Assumptions:

C11 contains 20% and 40%

A22 contains Destination, Basic, '13861mm to 18288mm', and '18289mm &
over'

A419 contains the data

F4 contains the 'Destination' of interest

G4 contains the 'Length' of interest

Formula:

=INDEX(C419,MATCH(F4,A4:A19,0),MATCH(G4,{13861,18289}))

If, instead, you let C22 contain 13861 and 18289, you could use the
following formula...

=INDEX(C419,MATCH(F4,A4:A19,0),MATCH(G4,C22))

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

Hope this helps!

 

[Max]

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 A218

Enter the numbers: 13861, 18289 into C11

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

---

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="scotland",C36>13861,C36<1828
9),80,IF(AND(A36="scotland",C36>18289),160,IF(AND(A36="Cumbria",C36<13861),0