Use of OR and AND function in same nested function

[Blade Fisher]

This is driving me nuts and I can't see why what I am doing won't work.

SR
CL
06:15

06:15
07:06
07:07
07:35
07:36
. . . .
. . . .
. . . .

The letters SR and CL are in cells N38 and N39. They are code for an origin (N38) and a destination (N39). According to the pairing of these and other codes the vertical time output shown in (N42:N46) changes. The 06:15 is a start time.

I am trying to write a formula for N47, which should be something like this

=IF(OR(N$38="AY",N$38="GV”,N$38="SR”),AND(N$39="CL",N$39="KM")),N$46+$A$28, ". . . .")

In other words if the following combination of origin and destination codes are put in to N38 and N39, (N$38="AY",N$38="GV”,N$38="SR”) and (N$39="CL",N$39="KM")), N47 will give me a result of adding a value to N46.

Can someone advise what that forumals should be and the syntax to use as I've tried most combinations I can think of and get nothing like the answer I need!

[bebo021999]

AND(N$39="CL",N$39="KM") always return FALSE

Maybe:

=IF(AND(OR(N$38="AY",N$38="GV”,N$38="SR”),OR(N$39="CL",N$39="KM")),N$46+$A$28, ". . . .")

[etaf]

the way you have the formula set out

=IF(OR(N$38="AY",N$38="GV”,N$38="SR”),AND(N$39="CL",N$39="KM")),N$46+$A$28, ". . . .")

OR(N$38="AY",N$38="GV”,N$38="SR”)
this the test as you have one set of brackets and a comma
so then the TRUE part is
AND(N$39="CL",N$39="KM"))
and also closes the IF statement completely

the AND also can never be true
AND(N$39="CL",N$39="KM")
as the same cell cannot be two things at once

what are the rules again

you need to combine the two different cells

can you write out - the result in a truth table

N38 --- N39 Result
AY --- CL --- ?
GV --- CL --- ?
SR --- CL --- ?
AY --- SR --- ?
GV --- SR --- ?
SR --- SR --- ?

from that you should be able to work out the where to use the AND / ORs

[Blade Fisher]

Hi,

It is the combination that is important.

The origin cell N38 can be either (N$38="AY",N$38="GV”,N$38="SR”) and the destination cell either (N$39="CL",N$39="KM"). What is important here is the combinations which are

AY-CL, AY-KM, GV-CL, GV-KM, SR-CL, SR-KM. If these combinations are true then the answer is N$46+$A$28. If not then ". . . ." is returned

[AliGW]

=if(and(or(n$38="ay",n$38="gv",n$38="sr"),or(n$39="cl",n$39="km")),n$46+$a$28,"")

[Blade Fisher]

Yes that has solved it! Thanks. It was the second OR that I wasn't getting.

Cheers!

[martindwilson]

you can shorten that a bit
=IF(AND(OR(N$38={"ay","gv","sr"}),OR(N$39={"cl","km"})),N$46+$A$28,".......")

[AliGW]

you can shorten that a bit
=IF(AND(OR(N$38={"ay","gv","sr"}),OR(N$39={"cl","km"})),N$46+$A$28,"")

Yes!

=IF(AND(OR(N$38={"ay","gv","sr"}),OR(N$39={"cl","km"})),N$46+$A$28,". . . .")

[AliGW]

=if(and(or(n$38="ay",n$38="gv",n$38="sr"),or(n$39="cl",n$39="km")),n$46+$a$28,". . . .")