用于查找最多 3 个值的公式，即使单元格值为空

发布于 2024-12-11 00:00:51 字数 330 浏览 2 评论 0原文

我有 4 列值。我想总结一下他们三者中最好的。我应该写什么公式？

如果任何列中的值为空或不是数字，则用于计算的其值应默认为 0。

示例：

| 4| 2| 1| 3|  (2,3,4 are best, sum = 9)
| 4| 1|AB| 5|  (1,4,5 are best, sum = 10)
|AB|AB| 6| 7|  (0,6,7 are best, sum = 13)
| 1|AB|  |  |  (0,0,1 are best, sum = 1)

如何将非数字字段默认为数字值进行计算？如何计算四列中最好的 3 列的总和？

原文

I have 4 columns with values. I want to sum best of three from them. What formula I should write?

If the value in any of the columns is empty or not a number, its value for the purpose of the calculation should default to 0.

Examples:

| 4| 2| 1| 3|  (2,3,4 are best, sum = 9)
| 4| 1|AB| 5|  (1,4,5 are best, sum = 10)
|AB|AB| 6| 7|  (0,6,7 are best, sum = 13)
| 1|AB|  |  |  (0,0,1 are best, sum = 1)

How do I make a non-number field default to a number value for calculations?
How do I sum just the best 3 of the four columns?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

影子的影子 2024-12-18 00:00:51

回答说明单元格中可能存在非数字（空也是非数字）

=SUM(A1:D1)-MIN(IF(ISNUMBER(A1),A1,0),IF(ISNUMBER(B1),B1,0),IF(ISNUMBER(C1),C1,0), IF(ISNUMBER(D1),D1,0))

ISNUMBER(_cell_) 对于数字返回 true，对于其他任何值返回 false。

Answer that accounts for Empty cells:

=SUM(A1:D1)-MIN(IF(ISBLANK(A1),0,A1),IF(ISBLANK(B1),0,B1),IF(ISBLANK(C1),0,C1),IF(ISBLANK(D1),0,D1))

ISBLANK(_cell_) 可用于检查单元格是否有值，IF(bool, value_if_true, value_if_false) 可用于根据布尔值在两个值之间进行选择价值。

Original Answer:
This is not portable for I want best X of Y fields, but does Best Y-1 out of Y

=SUM(A1:D1)-MIN(A1:D1)

您的值位于 A、B、C、D 列中

Answer that accounts for possible non-numbers in cells (Empty is also a non-number)

=SUM(A1:D1)-MIN(IF(ISNUMBER(A1),A1,0),IF(ISNUMBER(B1),B1,0),IF(ISNUMBER(C1),C1,0), IF(ISNUMBER(D1),D1,0))

ISNUMBER(_cell_) returns true for numbers, but false for anything else.

Answer that accounts for Empty cells:

=SUM(A1:D1)-MIN(IF(ISBLANK(A1),0,A1),IF(ISBLANK(B1),0,B1),IF(ISBLANK(C1),0,C1),IF(ISBLANK(D1),0,D1))

ISBLANK(_cell_) can be used to check if a cell has a value, IF(bool, value_if_true, value_if_false) can be used to choose between two values depending on the boolean value.

Original Answer:
This is not portable for I want best X of Y fields, but does Best Y-1 out of Y

=SUM(A1:D1)-MIN(A1:D1)

where your values are in columns A, B, C, D

回复收藏 0 原文

梦归所梦 2024-12-18 00:00:51

如果您只有四个值并且想要转储最低的值，那么这是一个快速解决方案。您的范围是 A1 到 A4。将它们全部相加并减去该组的最低值。

=SUM(A1:A4)-MIN(A1:A4)

这将对单元格不是“AB”的值进行求和。

=SUMIF(C1:C4,"<>AB")-MIN(C1:C4)

如果您想对除缺勤学生（“AB”）之外的所有值求和，则删除减去的值。

=SUMIF(C1:C4,"<>AB")

C1 = 2.5
C2 = AB
C3 = AB
C4 = 3
SUMIF = 5.5

此公式查看每个单元格，如果值为“AB”，则分配 0，然后从该范围中减去最小值。

=SUM(IF(C1="AB",0,C1),IF(C2="AB",0,C2),IF(C3="AB",0,C3),(IF(C4="AB",0,C4)))-MIN(IF(C1="AB",0,C1),IF(C2="AB",0,C2),IF(C3="AB",0,C3),(IF(C4="AB",0,C4)))

This is a quick solution if you only have four values and want to dump the lowest. Your range is A1 through A4. Sum them all and subtract the lowest value of the group.

=SUM(A1:A4)-MIN(A1:A4)

This will sum the values where the cells are not "AB"

=SUMIF(C1:C4,"<>AB")-MIN(C1:C4)

If you want to SUM all values, except for the absent students ("AB"), then remove the subtracted value.

=SUMIF(C1:C4,"<>AB")

C1 = 2.5
C2 = AB
C3 = AB
C4 = 3
SUMIF = 5.5

This formula looks at each cell, assigns a 0 if the value is "AB", then subtracts the lowest value from that range.

=SUM(IF(C1="AB",0,C1),IF(C2="AB",0,C2),IF(C3="AB",0,C3),(IF(C4="AB",0,C4)))-MIN(IF(C1="AB",0,C1),IF(C2="AB",0,C2),IF(C3="AB",0,C3),(IF(C4="AB",0,C4)))

回复收藏 0 原文