不包含Null值的Avg表达式 [英] Expression for Avg that does not include Null values

问题描述

这是我目前所拥有的


[[LineSpeed（Meters）1] + [LineSpeed（Meters）2] + [LineSpeed（Meters）3] + [LineSpeed（米）4] + [LineSpeed（Meters）5] + [LineSpeed（Meters）6] + [LineSpeed（Meters）7] + [LineSpeed（Meters）8]


我需要找到不包含null的字段的平均值。


示例：

添加第1行和第2行以及第3行，但不包括其他行不是数据，然后把它除以3（或者数字会是多少）


谢谢，


Kevin


PS。我忘了提到这是在表中的计算字段中。我正在使用Access 2016.

Here is what I have so far

[[LineSpeed(Meters)1]+[LineSpeed(Meters)2]+[LineSpeed(Meters)3]+[LineSpeed(Meters)4]+[LineSpeed(Meters)5]+[LineSpeed(Meters)6]+[LineSpeed(Meters)7]+[LineSpeed(Meters)8]

I need to find the avgerage of the fields that do not include null.

Example:
Add lines 1 and 2 and 3 but do not include the others as there isn''t data, then have it divided by the 3 (or what ever the number would be)

Thanks,

Kevin

PS. I forgot to mention this is in a calculated field in the table. I''m using Access 2016.

推荐答案

如果它是一个计算字段，那么我相信你被限制使用表达式而不是VBA。你可以通过调用VBA函数来逃避，但我对此表示怀疑。表达式当然比VBA更笨拙。


为了便于阅读和理解，我用简单的字母替换了你漫长而复杂的字段名称，我也减少了字段数为4，但这是我希望你需要的东西：

If it''s a calculated field then I believe you''re constrained to use expressions rather than VBA. It''s possible you could get away with calling a VBA function, but I doubt it. Expressions are far more clumsy than VBA of course.

For ease of reading and comprehension I''ve replaced your long, complicated field names with simple letters, and I''ve also reduced the number of fields to four, but this is the sort of thing I expect you''ll need :

展开 | 选择 | Wrap | 行号

I想知道两阶段方法是否有效。

首先是如果值为空则加0的总和

I wonder if a 2 stage approach would work.
First the sum which adds 0 if the value is null

展开 | 选择 | Wrap | 行号

您好菲尔。


您可能会注意到我的建议中的额外位，即处理所有项目为空的可能性。检查这一点的偷偷摸摸的方法是使用& 将它们全部附加在一起。只有全部为Null时，结果才为Null。否则，使用 Nz（[X]，0）将所有值相加，然后将此总数除以证明为非空的项目数。正如您在帖子中描述的那样。

Hi Phil.

You may notice the extra bit in my suggestion, which was to handle the possibility of all items being Null. A sneaky way to check for this is to append them all together using the &. Only if all are Null will the result be Null. Otherwise all the values are added together using Nz([X],0) then this total is divided by the number of items that prove to be non Null. Very much as you describe in your post.

这篇关于不包含Null值的Avg表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！