'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:

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,18289}))

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 <[email protected]>,
Lynneth <[email protected]> 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="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

[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 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
,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
>
>
> --
> Lynneth
> ------------------------------------------------------------------------
> Lynneth's Profile:
http://www.excelforum.com/member.php...o&userid=26102
> View this thread: http://www.excelforum.com/showthread...hreadid=508368
>