Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need some comments on my Utility_Move class module. | Excel Worksheet Functions | |||
Creating a Custom Excel Function to Calculate Gini Coefficients | Excel Worksheet Functions | |||
Date & Time | New Users to Excel | |||
Conversion | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |