Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formulas that matches data and subtract dates
I am trying to combine a formula that does two different things; matches
cities, a code for the city, and if it has a match, calculate the difference, in weeks between the two events. ex. Detroit, 1Pa, 04/06/04 Detroit, 1Pa, 06/23/04 Detroit, 1Pb, 08/01/04 Detroit, 1Pb, 11/05/04 I want to match the cities, and if they have a matching code, find out the time in weeks between the two event dates. Is this possible, and if so, HOW??? THANKS |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
formulas that matches data and subtract dates
Here's one crack at this ..
Assuming data in cols A to C, from row2 down Put in D2, and array-enter the formula, ie press CTRL+SHIFT+ENTER (instead of just pressing ENTER): =IF(SUMPRODUCT(($A$2:A2=A2)*($B$2:B2=B2)*($A$2:A2< "")*($B$2:B2<""))=2,C2-MIN(IF(($A$2:A2=A2)*($B$2:B2=B2),$C$2:C2)),"") Copy D2 down as far as required Col D will return the number of days between the 2 events on the "2nd event" line, viz.: Detroit 1Pa 06-04-2004 Detroit 1Pa 23-06-2004 78 Detroit 1Pb 01-08-2004 Detroit 1Pb 05-11-2004 96 And if you want the results in decimal weeks (ie # of days/7), then just put instead in D2, array-entered as before, and fill down: =IF(SUMPRODUCT(($A$2:A2=A2)*($B$2:B2=B2)*($A$2:A2< "")*($B$2:B2<""))=2,(C2-MIN(IF(($A$2:A2=A2)*($B$2:B2=B2),$C$2:C2)))/7,"") -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Sting17" wrote: I am trying to combine a formula that does two different things; matches cities, a code for the city, and if it has a match, calculate the difference, in weeks between the two events. ex. Detroit, 1Pa, 04/06/04 Detroit, 1Pa, 06/23/04 Detroit, 1Pb, 08/01/04 Detroit, 1Pb, 11/05/04 I want to match the cities, and if they have a matching code, find out the time in weeks between the two event dates. Is this possible, and if so, HOW??? THANKS |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
formulas that matches data and subtract dates
Hi Sting
One way Copy your data to another sheet - click on sheet tab, hold down Control, as you drag your cursor to the right. This will create a duplicate of the sheet. On this copy of the data, in column D enter =A1&B1 Copy down column D for the extent of your data. Mark the whole block of data, then DataSortColumnDAscending in column E enter the following in cell E2 =IF(D2<D1,"",(C2-C1)/7) You show your data as in consecutive pairs, but I guess in reality the data may be scattered throughout the table. The sort on the concatenated filed will bring all of the like data together -- Regards Roger Govier "Sting17" wrote in message ... I am trying to combine a formula that does two different things; matches cities, a code for the city, and if it has a match, calculate the difference, in weeks between the two events. ex. Detroit, 1Pa, 04/06/04 Detroit, 1Pa, 06/23/04 Detroit, 1Pb, 08/01/04 Detroit, 1Pb, 11/05/04 I want to match the cities, and if they have a matching code, find out the time in weeks between the two event dates. Is this possible, and if so, HOW??? THANKS |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
formulas that matches data and subtract dates
Thank you. I tried both and both worked. One issue that I found was that I
need to sort by the "P1" and "P2" of the P1a and P2a. The "P1" data is the pairing data, the "a", "b", and "c" just refers the time of the month it happened. I guess that I have to create another column to read that data as "P1" and "P2", then use that as the sort? Thank you very much. Sting "Roger Govier" wrote: Hi Sting One way Copy your data to another sheet - click on sheet tab, hold down Control, as you drag your cursor to the right. This will create a duplicate of the sheet. On this copy of the data, in column D enter =A1&B1 Copy down column D for the extent of your data. Mark the whole block of data, then DataSortColumnDAscending in column E enter the following in cell E2 =IF(D2<D1,"",(C2-C1)/7) You show your data as in consecutive pairs, but I guess in reality the data may be scattered throughout the table. The sort on the concatenated filed will bring all of the like data together -- Regards Roger Govier "Sting17" wrote in message ... I am trying to combine a formula that does two different things; matches cities, a code for the city, and if it has a match, calculate the difference, in weeks between the two events. ex. Detroit, 1Pa, 04/06/04 Detroit, 1Pa, 06/23/04 Detroit, 1Pb, 08/01/04 Detroit, 1Pb, 11/05/04 I want to match the cities, and if they have a matching code, find out the time in weeks between the two event dates. Is this possible, and if so, HOW??? THANKS |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
formulas that matches data and subtract dates
Can you reformulate to make it read the first two letter of the pairing code:
"1P" of the "1Pa"? I can have: Detroit 1Pa 01/01/01 Detroit 1Pb 03/01/01 These would be paired events even thought the letter"a" and "b" are different. So basiically, it would be everything that was "Detroit" and "1P" that I would want to apply the calculation to. Possible? Thanks. "Max" wrote: Here's one crack at this .. Assuming data in cols A to C, from row2 down Put in D2, and array-enter the formula, ie press CTRL+SHIFT+ENTER (instead of just pressing ENTER): =IF(SUMPRODUCT(($A$2:A2=A2)*($B$2:B2=B2)*($A$2:A2< "")*($B$2:B2<""))=2,C2-MIN(IF(($A$2:A2=A2)*($B$2:B2=B2),$C$2:C2)),"") Copy D2 down as far as required Col D will return the number of days between the 2 events on the "2nd event" line, viz.: Detroit 1Pa 06-04-2004 Detroit 1Pa 23-06-2004 78 Detroit 1Pb 01-08-2004 Detroit 1Pb 05-11-2004 96 And if you want the results in decimal weeks (ie # of days/7), then just put instead in D2, array-entered as before, and fill down: =IF(SUMPRODUCT(($A$2:A2=A2)*($B$2:B2=B2)*($A$2:A2< "")*($B$2:B2<""))=2,(C2-MIN(IF(($A$2:A2=A2)*($B$2:B2=B2),$C$2:C2)))/7,"") -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Sting17" wrote: I am trying to combine a formula that does two different things; matches cities, a code for the city, and if it has a match, calculate the difference, in weeks between the two events. ex. Detroit, 1Pa, 04/06/04 Detroit, 1Pa, 06/23/04 Detroit, 1Pb, 08/01/04 Detroit, 1Pb, 11/05/04 I want to match the cities, and if they have a matching code, find out the time in weeks between the two event dates. Is this possible, and if so, HOW??? THANKS |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
formulas that matches data and subtract dates
Hi Sting
If you want to omit the "a", "b" and "c" make the concatenation formula instead =A1&LEFT(B1,2) Proceed as before -- Regards Roger Govier "Sting17" wrote in message ... Thank you. I tried both and both worked. One issue that I found was that I need to sort by the "P1" and "P2" of the P1a and P2a. The "P1" data is the pairing data, the "a", "b", and "c" just refers the time of the month it happened. I guess that I have to create another column to read that data as "P1" and "P2", then use that as the sort? Thank you very much. Sting "Roger Govier" wrote: Hi Sting One way Copy your data to another sheet - click on sheet tab, hold down Control, as you drag your cursor to the right. This will create a duplicate of the sheet. On this copy of the data, in column D enter =A1&B1 Copy down column D for the extent of your data. Mark the whole block of data, then DataSortColumnDAscending in column E enter the following in cell E2 =IF(D2<D1,"",(C2-C1)/7) You show your data as in consecutive pairs, but I guess in reality the data may be scattered throughout the table. The sort on the concatenated filed will bring all of the like data together -- Regards Roger Govier "Sting17" wrote in message ... I am trying to combine a formula that does two different things; matches cities, a code for the city, and if it has a match, calculate the difference, in weeks between the two events. ex. Detroit, 1Pa, 04/06/04 Detroit, 1Pa, 06/23/04 Detroit, 1Pb, 08/01/04 Detroit, 1Pb, 11/05/04 I want to match the cities, and if they have a matching code, find out the time in weeks between the two event dates. Is this possible, and if so, HOW??? THANKS |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
formulas that matches data and subtract dates
Assuming that the source data is already/always in pairs from row2 down, eg:
Detroit 1Pa 06-04-2004 Detroit 1Pa 23-06-2004 Detroit 1Pb 01-08-2004 Detroit 1Pb 05-11-2004 Miami 1Pa 06-04-2004 Miami 1Pa 23-06-2004 Chicago 1Pb 01-08-2004 Chicago 1Pb 05-11-2004 etc Then perhaps this might suffice in D2, copied down: =IF(OR(A2="",B2=""),"",IF(MOD(ROW(),2)=1,C2-C1,"")) to return the number of days between the 2 events on every "2nd event" line -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Sting17" wrote: Can you reformulate to make it read the first two letter of the pairing code: "1P" of the "1Pa"? I can have: Detroit 1Pa 01/01/01 Detroit 1Pb 03/01/01 These would be paired events even thought the letter"a" and "b" are different. So basiically, it would be everything that was "Detroit" and "1P" that I would want to apply the calculation to. Possible? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Line chart or scatter chart?? | Charts and Charting in Excel | |||
Ideas for simplifying cell formulas | Excel Discussion (Misc queries) | |||
VLOOKUP calendar dates | Excel Discussion (Misc queries) | |||
Help with data not getting plotted | Excel Discussion (Misc queries) | |||
Help with data not getting plotted | Charts and Charting in Excel |