英文:
How to determine averages by multiple criteria including ignoring zeros in the calculations
问题
=averageifs(b:b, ">0", c:c, "Fresh") ... 返回 "您为此函数输入的参数太少。"
也尝试过：=averageifs(b:b, ">0", a:a, e2, c:c, f1) ... 结果相同
英文:
I'm trying to create an averageifs statement that will go through a dataset and for each month, calculate the average (ignoring zero values) of two different columns). I'm not sure how to add a sample dataset to this question?
=averageifs(b:b, ">0", c:c, "Fresh") ... yields "you've entered too few arguments for this function."
also tried: =averageifs(b:b, ">0", a:a, e2, c:c, f1) ... same result
One can be downloaded from here: https://image-files-manausa.s3.us-west-2.amazonaws.com/Stack_Overflow_Example.xlsx
A screen capture is below:
答案1
得分: 1
只是展示 averageifs() 或任何 xxxifs() 函数中设置条件的三种方式的示例：
我使用第三个版本，其中引用了一个单元格，因为这是最灵活的。
英文:
Just to show 3 ways of setting criteria in averageifs() or any of the xxxifs() functions:
I use the 3rd version where a cell is referenced as that is the most flexible.
答案2
得分: 0
我添加了一些命名范围以避免混淆。这是一个可行的解决方案：
=IFERROR(AVERAGEIFS(Dates, Time, ">0", Dates, ">=" & E2, Dates, "<=" & EDATE(E2,1), Type, $F$1), "")
其中：
- Dates 是 a2:a35
- Time 是 b2:b35
- Type 是 c2:c35
英文:
I added a few named ranges to keep my head from spinning. This is a working solution:
=IFERROR(AVERAGEIFS(Time,Time,">0",Dates,">="&E2,Dates,"<="&EDATE(E2,1),Type,$F$1),"")
Where Dates is a2:a35
Time is b2:b35
Type is c2:c35
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论