如何使用AverageIf工作表函数计算平均日期差(如果填写日期) [英] How to use AverageIf worksheet function for calculating average date differences (in case date is filled in)

问题描述

我正在对任务列表以及相应的日期(开始日期，首次回答日期，...)进行汇总.

I'm making a summary of a list of tasks, and the corresponding dates (start date, first answer date, ...).

看起来或多或少如下:

Title    Start date    First answer
Task1     29/06/2018   02/07/2018
Task2     09/05/2018   
Task3     13/06/2018   14/06/2018

我想计算给出第一个答案所需的平均时间.如果尚未给出第一个答案，则在计算平均值时需要忽略此条目.

I would like to calculate the average time, needed for the first answer to be given. In case no first answer is given yet, this entry needs to be ignored in the calculation of the average.

为了能够更好地理解公式，我决定对标题使用名称，例如:

In order to be able to understand the formulas better, I've decided to use names for the headers, like:

Name "Header_Title"        has value "Title"
Name "Header_Start_Date"   has value "Start Date"
Name "Header_First_Answer" has value "First answer"

此外，定义为COUNTA(OFFSET(Header_Title;1;0):A1048576)的条目数具有名称:Total_Count.

Also, the number of entries, defined as COUNTA(OFFSET(Header_Title;1;0):A1048576) has a name: Total_Count.

接下来，我为列值的范围创建了名称:

Next to that, I've created names for the ranges of the column values:

"All_Start_Dates"    is defined as =OFFSET(Header_Start_Date;1;0):OFFSET(Header_Start_Date;Total_Count;0)
"All_First_Answered" is defined as =OFFSET(Header_First_Answer;1;0):OFFSET(Header_First_Answer;Total_Count;0)

说明:在标题(列标题)下获得第一个条目，并转到与最后一个任务相对应的行.

Explanation : take the first entry under the header (column title) and go to the row, corresponding to the last task.

这使得编写公式来计算这些日期列之间的平均差非常容易:

This makes it very easy to write a formula for calculating the average difference between those date columns:

{=AVERAGE(All_First_Answered_Dates-All_Start_Dates)}
// mind the {} for showing this is an array formula

现在的问题是:如何使用AverageIf工作表功能，以便不考虑未填写First answer的情况?
我已经尝试过使用">0"和">"&0，但这是行不通的，据说这些公式无效:

Now the problem is: how to use the AverageIf worksheet function in order not to take into account the cases where First answer is not filled in?
I already tried using ">0" and ">"&0, but this does not work, the formulas are said not to be valid:

{=AVERAGEIF(All_First_Answered_Dates-All_Start_Dates;">0")}
{=AVERAGEIF(All_First_Answered_Dates-All_Start_Dates;">"&0)}

有人有主意吗?
预先感谢

Does anybody have an idea?
Thanks in advance

P.s.1.如您所见，我将单元格范围A1048576作为列A的最后一个条目，有人知道这种方法更优雅吗?
P.s.2.另外一件使我的生活更加轻松的事情是，可以查看哪些单元格具有名称(我当时在考虑条件格式，但是我没有找到实现此目的的方法).有人知道是否有一种方法可以突出显示与名称链接的单个单元格吗?

P.s.1. As you can see, I'm using cell range A1048576 as the last entry of column A, does anybody know a more elegant way to describe this?
P.s.2. One extra thing, which would make my life easier, is the possibility to see which cells have a name (I was thinking about conditional formatting, but I didn't find the way to do this). Does anybody know if there is a way to highlight individual cells, linked to a name?

推荐答案

所以我建议的答案是

=AVERAGEIF(All_First_Answered,">0")-AVERAGEIF(All_First_Answered,">0",All_Start_Dates)

我在这里假设所有开始日期都存在，但是可能缺少第一个回答日期:如果您使用AVERAGEIFS，则可以轻松地为开始日期添加一个附加条件.公式的两个部分都包含相同的条件，因此它们在同一行上工作.

I'm assuming here that all start dates are present but first answered dates may be missing: you could easily add an extra condition for start date if you used AVERAGEIFS. Both parts of the formula include the same conditions so they are working on the same rows.

上面的中间几列仅作为说明.

The intermediate columns above are just included by way of explanation.

这篇关于如何使用AverageIf工作表函数计算平均日期差(如果填写日期)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！