why CSE formulas and basic equivalents treat 0s differently?

[grey fox]

Does anyone know why and exactly how CSE formulas and basic equivalents treat 0s differently?

For example, I am trying to average long data strings. If I do a {=AVERAGE(IF(Test!B4:B1001=Test!F4,Test!D4:D1001))} it averages differently than the simple average due to 0s in the lineup. Does anyone know how to remedy that?

 

[Juan Pablo González]

I'm not sure I follow, can you provide a small example of what you're getting/need ?

 

[Aladin Akyurek]

On 2002-05-12 23:31, grey fox wrote:
Does anyone know why and exactly how CSE formulas and basic equivalents treat 0s differently?

For example, I am trying to average long data strings. If I do a {=AVERAGE(IF(Test!B4:B1001=Test!F4,Test!D4:D1001))} it averages differently than the simple average due to 0s in the lineup. Does anyone know how to remedy that?

Also, you can rewrite that array formula in terms of SUMIF:

=SUMIF(Test!B4:B1001,Test!F4,Test!D4:D1001)/MAX(1,COUNTIF(Test!B4:B1001,Test!F4))