我有大量实验报告,结果小于 20 mg/L。我试图对一列数字取几何平均值,例如 <20、<20、<20、20、1400、2500、<20、<20。我注意到,当我去掉“<”时,几何平均值会发生变化。我如何欺骗 Excel 让它认为它是一个实数?
答案1
尝试这个数组公式:
=GEOMEAN(VALUE(SUBSTITUTE(A1:A6,"<","")))
此公式一次性完成所有操作。无需IF
语句,无需拖拽公式。它循环遍历 A1 到 A6,用空格替换任何“<”,获取值,然后对结果值取几何平均值。
您所要做的就是A1:A6
用您想要的几何平均值的范围进行替换。
重要的:使用数组公式,您需要输入公式,然后按:
Ctrl- Shift-Enter
(如果仅按Enter,则不起作用。)
如果操作正确,{ }
公式周围会出现括号,如下所示:
{=GEOMEAN(VALUE(SUBSTITUTE(A1:A6,"<","")))} // don't type in the braces, they will appear automatically
答案2
使用自定义单元格格式。
答案3
您需要在另一列中创建一个公式,该公式可以直接读取值,或者如果有“<”,则获取值部分。
添加 0 或自定义单元格格式将会改变 Geomean。
编辑
假设 A1 中有“<20”
在 B1 中写入:=MID(A1;1;1) 在 C1 中写入:=IF(B1="<";20;A1)
C1 中的答案现在应该是 20。如果将 A1 更改为 5,则 C1 也应该更改为 5。
答案4
假设您的数据在列 A 中并从第 1 行开始,数字 <20 的范围平均为 10,因此只要有 <20
然后b列的公式是 =IF(A1="<20",10,A1)
并将公式向下拖动到与 A 列中的数据相同的行数。
或者要更改 A 列中的数字,请使用“编辑”-->“替换”将所有 <20 更改为 10