| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date difference Average
I have 2 column as below and I want to calculate the average number of days
ABC occurs.. In this case it should be 3.33 Col A Col B 01-Apr ABC 01-Apr DBA 01-Apr ABC 03-Apr GRT 05-Apr HTA 05-Apr JYU 08-Apr ABC 08-Apr GRT 08-Apr HTA 10-Apr JYU 11-Apr ABC |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date difference Average
Hi,
Please explain why the answer is 3.33. I think 4 of 11 in your list are ABC so 4/11 =36.36% or with this formula =COUNTIF(B1:B11,"abc")/COUNTA(B1:B11) You don't explain the relevance of the dates. Mike Mike "Kashyap" wrote: I have 2 column as below and I want to calculate the average number of days ABC occurs.. In this case it should be 3.33 Col A Col B 01-Apr ABC 01-Apr DBA 01-Apr ABC 03-Apr GRT 05-Apr HTA 05-Apr JYU 08-Apr ABC 08-Apr GRT 08-Apr HTA 10-Apr JYU 11-Apr ABC |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date difference Average
it should be 3.33
Can you explain how you arrive at that result? -- Biff Microsoft Excel MVP "Kashyap" wrote in message ... I have 2 column as below and I want to calculate the average number of days ABC occurs.. In this case it should be 3.33 Col A Col B 01-Apr ABC 01-Apr DBA 01-Apr ABC 03-Apr GRT 05-Apr HTA 05-Apr JYU 08-Apr ABC 08-Apr GRT 08-Apr HTA 10-Apr JYU 11-Apr ABC |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date difference Average
date when ABC occurs is
01-Apr 01-Apr 0 08-Apr 7 11-Apr 3 3.33 --- "T. Valko" wrote: it should be 3.33 Can you explain how you arrive at that result? -- Biff Microsoft Excel MVP "Kashyap" wrote in message ... I have 2 column as below and I want to calculate the average number of days ABC occurs.. In this case it should be 3.33 Col A Col B 01-Apr ABC 01-Apr DBA 01-Apr ABC 03-Apr GRT 05-Apr HTA 05-Apr JYU 08-Apr ABC 08-Apr GRT 08-Apr HTA 10-Apr JYU 11-Apr ABC |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date difference Average
Ok, I get it.
This is pretty complicated to try in a single formula so I would use a helper column. Assume your data is in the range A2:B12. Enter this formula in *C3* and copy down to C12: =IF(B3<"abc","",A3-LOOKUP(2,1/(B$2:B2="abc"),A$2:A2)) Then to get the average: =AVERAGE(C3:C12) -- Biff Microsoft Excel MVP "Kashyap" wrote in message ... date when ABC occurs is 01-Apr 01-Apr 0 08-Apr 7 11-Apr 3 3.33 --- "T. Valko" wrote: it should be 3.33 Can you explain how you arrive at that result? -- Biff Microsoft Excel MVP "Kashyap" wrote in message ... I have 2 column as below and I want to calculate the average number of days ABC occurs.. In this case it should be 3.33 Col A Col B 01-Apr ABC 01-Apr DBA 01-Apr ABC 03-Apr GRT 05-Apr HTA 05-Apr JYU 08-Apr ABC 08-Apr GRT 08-Apr HTA 10-Apr JYU 11-Apr ABC |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date difference Average
Enter this formula in *C3* and copy down to C12:
=IF(B3<"abc","",A3-LOOKUP(2,1/(B$2:B2="abc"),A$2:A2)) I found a bug in that formula. Use this one: =IF(B3="abc",IF(COUNTIF(B$2:B3,"abc")1,A3-LOOKUP(2,1/(B$2:B2="abc"),A$2:A2),""),"") While we're at it: Then to get the average: =AVERAGE(C3:C12) Let's change that formula to: =IF(COUNTIF(C3:C12,"0"),AVERAGE(C3:C12),0) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Ok, I get it. This is pretty complicated to try in a single formula so I would use a helper column. Assume your data is in the range A2:B12. Enter this formula in *C3* and copy down to C12: =IF(B3<"abc","",A3-LOOKUP(2,1/(B$2:B2="abc"),A$2:A2)) Then to get the average: =AVERAGE(C3:C12) -- Biff Microsoft Excel MVP "Kashyap" wrote in message ... date when ABC occurs is 01-Apr 01-Apr 0 08-Apr 7 11-Apr 3 3.33 --- "T. Valko" wrote: it should be 3.33 Can you explain how you arrive at that result? -- Biff Microsoft Excel MVP "Kashyap" wrote in message ... I have 2 column as below and I want to calculate the average number of days ABC occurs.. In this case it should be 3.33 Col A Col B 01-Apr ABC 01-Apr DBA 01-Apr ABC 03-Apr GRT 05-Apr HTA 05-Apr JYU 08-Apr ABC 08-Apr GRT 08-Apr HTA 10-Apr JYU 11-Apr ABC |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date difference Average
Thank you Valko.. This seems to be working fine. But how to implement this
was all the names and not only abc? I have a list of unique name in ColE "T. Valko" wrote: Enter this formula in *C3* and copy down to C12: =IF(B3<"abc","",A3-LOOKUP(2,1/(B$2:B2="abc"),A$2:A2)) I found a bug in that formula. Use this one: =IF(B3="abc",IF(COUNTIF(B$2:B3,"abc")1,A3-LOOKUP(2,1/(B$2:B2="abc"),A$2:A2),""),"") While we're at it: Then to get the average: =AVERAGE(C3:C12) Let's change that formula to: =IF(COUNTIF(C3:C12,"0"),AVERAGE(C3:C12),0) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Ok, I get it. This is pretty complicated to try in a single formula so I would use a helper column. Assume your data is in the range A2:B12. Enter this formula in *C3* and copy down to C12: =IF(B3<"abc","",A3-LOOKUP(2,1/(B$2:B2="abc"),A$2:A2)) Then to get the average: =AVERAGE(C3:C12) -- Biff Microsoft Excel MVP "Kashyap" wrote in message ... date when ABC occurs is 01-Apr 01-Apr 0 08-Apr 7 11-Apr 3 3.33 --- "T. Valko" wrote: it should be 3.33 Can you explain how you arrive at that result? -- Biff Microsoft Excel MVP "Kashyap" wrote in message ... I have 2 column as below and I want to calculate the average number of days ABC occurs.. In this case it should be 3.33 Col A Col B 01-Apr ABC 01-Apr DBA 01-Apr ABC 03-Apr GRT 05-Apr HTA 05-Apr JYU 08-Apr ABC 08-Apr GRT 08-Apr HTA 10-Apr JYU 11-Apr ABC |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date difference Average
I don't think this can be done without using a helper column. If you can
sort your data by column B and then by column A so that all the same codes are grouped together then you could use just a single helper column. Otherwise, you'd need a helper column for each different code which may not be practical. For example, sort the data by column B (ascending order) then by column A (ascending order) so that the data ends up like this: 01-Apr...ABC 01-Apr...ABC 08-Apr...ABC 11-Apr...ABC 01-Apr...DBA 03-Apr...GRT 08-Apr...GRT 05-Apr...HTA 08-Apr...HTA 05-Apr...JYU 10-Apr...JYU Then, in column C starting in cell C3 you can enter this formula: =IF(B3=B2,A3-A2,"") This will return the differences between dates: 01-Apr...ABC.... 01-Apr...ABC...0 08-Apr...ABC...7 11-Apr...ABC...3 01-Apr...DBA.... 03-Apr...GRT.... 08-Apr...GRT...5 05-Apr...HTA.... 08-Apr...HTA...3 05-Apr...JYU.... 10-Apr...JYU...5 If you have a list of the unique codes in column E: E2 = ABC E3 = DBA E4 = GRT E5 = HTA E6 = JYU To get the averages enter this formula in F2 and copy down to F6: =IF(SUMIF(B$2:B$12,E2,C$2:C$12),SUMIF(B$2:B$12,E2, C$2:C$12)/SUMPRODUCT(--(B$2:B$12=E2),--(C$2:C$12<"")),"") -- Biff Microsoft Excel MVP "Kashyap" wrote in message ... Thank you Valko.. This seems to be working fine. But how to implement this was all the names and not only abc? I have a list of unique name in ColE "T. Valko" wrote: Enter this formula in *C3* and copy down to C12: =IF(B3<"abc","",A3-LOOKUP(2,1/(B$2:B2="abc"),A$2:A2)) I found a bug in that formula. Use this one: =IF(B3="abc",IF(COUNTIF(B$2:B3,"abc")1,A3-LOOKUP(2,1/(B$2:B2="abc"),A$2:A2),""),"") While we're at it: Then to get the average: =AVERAGE(C3:C12) Let's change that formula to: =IF(COUNTIF(C3:C12,"0"),AVERAGE(C3:C12),0) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Ok, I get it. This is pretty complicated to try in a single formula so I would use a helper column. Assume your data is in the range A2:B12. Enter this formula in *C3* and copy down to C12: =IF(B3<"abc","",A3-LOOKUP(2,1/(B$2:B2="abc"),A$2:A2)) Then to get the average: =AVERAGE(C3:C12) -- Biff Microsoft Excel MVP "Kashyap" wrote in message ... date when ABC occurs is 01-Apr 01-Apr 0 08-Apr 7 11-Apr 3 3.33 --- "T. Valko" wrote: it should be 3.33 Can you explain how you arrive at that result? -- Biff Microsoft Excel MVP "Kashyap" wrote in message ... I have 2 column as below and I want to calculate the average number of days ABC occurs.. In this case it should be 3.33 Col A Col B 01-Apr ABC 01-Apr DBA 01-Apr ABC 03-Apr GRT 05-Apr HTA 05-Apr JYU 08-Apr ABC 08-Apr GRT 08-Apr HTA 10-Apr JYU 11-Apr ABC |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Average of difference between dates greater than zero | Excel Worksheet Functions | |||
| Average of a difference between 2 cels in a table/array | Excel Worksheet Functions | |||
| Average the Difference between three dates in a pivot table | Excel Discussion (Misc queries) | |||
| Average Time Difference | Excel Worksheet Functions | |||
| Calculate difference between 2 date and times with average | Excel Discussion (Misc queries) | |||
Linear Mode
