why CSE formulas and basic equivalents treat 0s differently?

grey fox · May 13th, 2002, 12:31 AM

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 · May 13th, 2002, 12:33 AM

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

Aladin Akyurek · May 13th, 2002, 01:34 AM

Quote:

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))

May 13th, 2002, 12:33 AM	#2
Juan Pablo González MrExcel MVP Join Date: Feb 2002 Location: Bogota, Colombia Posts: 11,927	I'm not sure I follow, can you provide a small example of what you're getting/need ? __________________ Regards, Juan Pablo González http://www.juanpg.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 13th, 2002, 12:31 AM	#1
grey fox New Member Join Date: May 2002 Posts: 1	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?