需要替换Excel 2003的AVERAGEIFS公式 [英] Need to replace AVERAGEIFS formula for excel 2003
问题描述
我一直在试图掌握excel公式的日子,现在有一个140个单元格的电子表格,每个公式的配置略有不同。不幸的是,现在需要将其放弃到 Excel 2003 。
我试图得到我的头圆数组公式,而 SUMPRODUCT
,但我陷入僵局。
新鲜的眼睛,任何人?
这是我需要从2010年转换到2003年的公式: p>
= AVERAGEIFS(Data!S:S,Data!L:L,Atherstone,Data!T:T,Service ,数据!C:C,> =& K3,数据!C:C=& K4)
K3
是一个日期范围开始, K4
作为结束, 数据!
指的是 Sheet 2
所有数据的持有。
(我知道我的所有单元格范围都必须是明确的 - 即C2:c65536)。
需要将您的范围指定为固定区域,因为它不会理解 S:S
。
您的公式将最终看起来像这样:
= AVERAGE(IF(Data!L2:L2000 =Atherstone,IF(Data! T2000 =Service,IF(Data!C2:C2000> = K3,IF(Data!C2:C2000 <= K4,Data!S2:S2000))))))
(未验证)
你还需要使其成为数组公式,因此,通过使用 CTRL + SHIFT 输入公式,excel可以单独查看范围中的每个值。 kbd> + 输入,而不是更完整的输入> 输入
I've been trying to get to grips with excel formulae for days, and now have a spreadsheet with 140 cells, each with a slightly different formula. Unfortunately, it now needs to be 'dumbed down' to Excel 2003.
I've tried to get my head round array formulae, and SUMPRODUCT
, but I'm getting bogged down.
Fresh eyes, anyone?
This is the formula I need to convert from 2010 to 2003:
=AVERAGEIFS(Data!S:S,Data!L:L,"Atherstone",Data!T:T,"Service",Data!C:C,">="&K3,Data!C:C,"<="&K4)
K3
is a date range start, with K4
being the end, and Data!
refers to Sheet 2
where all the data is held.
(I know all my cell ranges will have to be explicit - i.e. C2:c65536).
You will need to specify your range as a fixed area, as it will not understand S:S
.
your formula will end up looking something like this:
=AVERAGE(IF(Data!L2:L2000="Atherstone",IF(Data!T2:T2000="Service",IF(Data!C2:C2000>=K3,IF(Data!C2:C2000<=K4,Data!S2:S2000)))))
(Untested)
you will also need to make it an Array formula, so excel can look at each of the values in the range individually by entering the formula using CTRL+SHIFT+Enter, instead of the more usual Enter after you have finished typing everything
这篇关于需要替换Excel 2003的AVERAGEIFS公式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!