| 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 |
| 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) | |||
Hybrid Mode
