Averageifs#DIV/0！解决方案? [英] Averageifs #DIV/0! solution?

问题描述

我知道这个问题已经问了100遍了，但是... 所以我有这个公式给我#DIV/0!.

I know this question has been asked 100's of times, but... So I have this formula that gives me #DIV/0!.

=AVERAGEIFS(Sheet1!L2:L, Sheet1!M2:M, "WIN",Sheet1!E2:E, ">=" &A7, Sheet1!E2:E, "<" &A8)

我知道我可以将其包裹在IFERROR(formula,"")或IFERROR(formula,0)周围，并且可以工作.但是如果将来出现真正的错误，它就会有问题.

I know I can wrap it around IFERROR(formula,"") or IFERROR(formula,0) and it works. But there are problems with it in case of real errors with it in the future.

我试图将其包裹在IF(formula,"")或IF(formula,0)周围.但这不起作用，我仍然得到相同的#DIV/0!.

I tried to wrap it around IF(formula,"") or IF(formula,0). But it doesn't work, I still get the same #DIV/0!.

这些又如何呢，我可以用一些东西不要在空单元格中显示0吗?

And how about these, can I use something not to show 0 in the empty cells?

=COUNTIFS(Sheet1!M2:M, "WIN",Sheet1!E2:E, ">=" &A7, Sheet1!E2:E, "<" &A8)

=SUMIFS(Sheet1!L2:L, Sheet1!M2:M, "LOSS",Sheet1!E2:E, ">=" &A7, Sheet1!E2:E, "<" &A8)

我尝试了上面的IF(...,"")和IF(...,0)，但是得到了#DIV/0!，ERROR或FALSE.

I tried the above IF(...,"") and IF(...,0) and I either get #DIV/0!, ERROR or FALSE.

编辑

为澄清起见，我正在研究工作表2"

So for clarification I'm working on sheets 2 "sample doc" included. This spreadsheet is to keep track of my trades that I will fill out over time. That's why most of sheet 1 is empty at the time. As you can see there's a lot of #DIV?0! on sheet 2. I know I get these because there's no match to most of these on sheet 1. Some of them will get filled out over time, but some of them might not get filled out for a while. As you can see the formulas work with the couple of "test" entries I made on sheet 1 to make sure everything works.

这就是为什么我要求一个更好的解决方案，然后将公式包装在IFERROR(...，")中的原因.但是，如果那是唯一，最好的解决方案，那就去吧.

That's why I asked for a better solution then wrapping the formulas in IFERROR(...,""). But if that's the only and best solution then so be it.

示例文档链接

谢谢.

推荐答案

=IFERROR(
 IF(AND(M:M="WIN",
        ISNUMBER(A7),
        ISNUMBER(a8),
        ISNUMBER(E:E),
        ISNUMBER(L:L)), AVERAGEIFS(Sheet1!L2:L, Sheet1!M2:M, "WIN",
                                                Sheet1!E2:E, ">="&A7, 
                                                Sheet1!E2:E, "<"&A8), ),
 "anverageifs error")

这篇关于Averageifs#DIV/0！解决方案?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！