AND function?
 

This might take a while to explain: I am not all that familiar with
excel.

In column A have a number (representing an area- from 1-14). In column
D, I have the time. In column E, I have a code. I want to find out how
many times any code is found with another code in the same area in the
space of 5-10 minutes (=5 mins, =<10 mins). There can only be a
maximum of two codes in an area at one time. If two codes are found in
that time range, then I want to concatenate the codes.

I previously did this in column F for =<5 mins (which is 0.00347222222
expressed in days).

in column F I typed:
=IF(AND(D3-D2<=0.00347222222,A2=A3,E2<E3),IF(E2<E3,CONCATENA TE(E2,E3),CONCATENATE(E3,E2)),)

This worked fine!

For between =5 mins and =<10 mins (expressed in days) I typed:
=IF(AND(D3-D2=0.00347222222,<=0.00694444444,A2=A3,E2<E3),IF (E2<E3,CONCATENATE(E2,E3),CONCATENATE(E3,E2)),)

It doesn't work! Why? What do you suggest?

AND function?
 

you forgot to type what was =< 10 mins.
This should work for you:
=IF(AND(D3-D2=0.00347222222,D3-D2<=0.00694444444,A2=A3,E2<E3),IF(E2<E3,CONCATE*N ATE(E2,E3),CONCATENATE(E3,E2)),)

fish wrote:
This might take a while to explain: I am not all that familiar with
excel.

In column A have a number (representing an area- from 1-14). In column
D, I have the time. In column E, I have a code. I want to find out how
many times any code is found with another code in the same area in the
space of 5-10 minutes (=5 mins, =<10 mins). There can only be a
maximum of two codes in an area at one time. If two codes are found in
that time range, then I want to concatenate the codes.

I previously did this in column F for =<5 mins (which is 0.00347222222
expressed in days).

in column F I typed:
=IF(AND(D3-D2<=0.00347222222,A2=A3,E2<E3),IF(E2<E3,CONCATENA TE(E2,E3),CONCATENATE(E3,E2)),)

This worked fine!

For between =5 mins and =<10 mins (expressed in days) I typed:
=IF(AND(D3-D2=0.00347222222,<=0.00694444444,A2=A3,E2<E3),IF (E2<E3,CONCATENATE(E2,E3),CONCATENATE(E3,E2)),)

It doesn't work! Why? What do you suggest?

AND function?
 

instead of computing the decimal for 5 minutes, you could use
TIMEVALUE("00:05:00") - it would be easier to make changes later.


"fish" wrote:

This might take a while to explain: I am not all that familiar with
excel.

In column A have a number (representing an area- from 1-14). In column
D, I have the time. In column E, I have a code. I want to find out how
many times any code is found with another code in the same area in the
space of 5-10 minutes (=5 mins, =<10 mins). There can only be a
maximum of two codes in an area at one time. If two codes are found in
that time range, then I want to concatenate the codes.

I previously did this in column F for =<5 mins (which is 0.00347222222
expressed in days).

in column F I typed:
=IF(AND(D3-D2<=0.00347222222,A2=A3,E2<E3),IF(E2<E3,CONCATENA TE(E2,E3),CONCATENATE(E3,E2)),)

This worked fine!

For between =5 mins and =<10 mins (expressed in days) I typed:
=IF(AND(D3-D2=0.00347222222,<=0.00694444444,A2=A3,E2<E3),IF (E2<E3,CONCATENATE(E2,E3),CONCATENATE(E3,E2)),)

It doesn't work! Why? What do you suggest?

AND function?
 

I have tried:
=IF(AND(D3-D2=0.00347222222,D3-D2<=0.00694444444,A2=A3,E2<E3),IF(E2<E3,CONCATENA TE(E2,E3),CONCATENATE(E3,E2)),)

AND I have tried:

=IF(AND(D3-D2=TIMEVALUE("00:05:00"),D3-D2<=TIMEVALUE("00:10:00"),A2=A3,E2<E3),IF(E2<E3,C ONCATENATE(E2,E3),CONCATENATE(E3,E2)),)

It still doesn't work. Just a zero appears where it is supposed to
concatenate. I don't get it.

JMB wrote:
instead of computing the decimal for 5 minutes, you could use
TIMEVALUE("00:05:00") - it would be easier to make changes later.


"fish" wrote:

This might take a while to explain: I am not all that familiar with
excel.

In column A have a number (representing an area- from 1-14). In column
D, I have the time. In column E, I have a code. I want to find out how
many times any code is found with another code in the same area in the
space of 5-10 minutes (=5 mins, =<10 mins). There can only be a
maximum of two codes in an area at one time. If two codes are found in
that time range, then I want to concatenate the codes.

I previously did this in column F for =<5 mins (which is 0.00347222222
expressed in days).

in column F I typed:
=IF(AND(D3-D2<=0.00347222222,A2=A3,E2<E3),IF(E2<E3,CONCATENA TE(E2,E3),CONCATENATE(E3,E2)),)

This worked fine!

For between =5 mins and =<10 mins (expressed in days) I typed:
=IF(AND(D3-D2=0.00347222222,<=0.00694444444,A2=A3,E2<E3),IF (E2<E3,CONCATENATE(E2,E3),CONCATENATE(E3,E2)),)

It doesn't work! Why? What do you suggest?

AND function?
 

I have just tried both of the formulas again, and they both work- I
think it might be taking a while to update the spreadsheet.
Thanks a lot to the two people who helped me solve this problem.

fish wrote:
I have tried:
=IF(AND(D3-D2=0.00347222222,D3-D2<=0.00694444444,A2=A3,E2<E3),IF(E2<E3,CONCATENA TE(E2,E3),CONCATENATE(E3,E2)),)

AND I have tried:

=IF(AND(D3-D2=TIMEVALUE("00:05:00"),D3-D2<=TIMEVALUE("00:10:00"),A2=A3,E2<E3),IF(E2<E3,C ONCATENATE(E2,E3),CONCATENATE(E3,E2)),)

It still doesn't work. Just a zero appears where it is supposed to
concatenate. I don't get it.

JMB wrote:
instead of computing the decimal for 5 minutes, you could use
TIMEVALUE("00:05:00") - it would be easier to make changes later.


"fish" wrote:

This might take a while to explain: I am not all that familiar with
excel.

In column A have a number (representing an area- from 1-14). In column
D, I have the time. In column E, I have a code. I want to find out how
many times any code is found with another code in the same area in the
space of 5-10 minutes (=5 mins, =<10 mins). There can only be a
maximum of two codes in an area at one time. If two codes are found in
that time range, then I want to concatenate the codes.

I previously did this in column F for =<5 mins (which is 0.00347222222
expressed in days).

in column F I typed:
=IF(AND(D3-D2<=0.00347222222,A2=A3,E2<E3),IF(E2<E3,CONCATENA TE(E2,E3),CONCATENATE(E3,E2)),)

This worked fine!

For between =5 mins and =<10 mins (expressed in days) I typed:
=IF(AND(D3-D2=0.00347222222,<=0.00694444444,A2=A3,E2<E3),IF (E2<E3,CONCATENATE(E2,E3),CONCATENATE(E3,E2)),)

It doesn't work! Why? What do you suggest?