AverageIf Question --

%3CLINGO-SUB%20id%3D%22lingo-sub-1452387%22%20slang%3D%22en-US%22%3EAverageIf%20Question%20--%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1452387%22%20slang%3D%22en-US%22%3E%3CH1%20id%3D%22toc-hId-471052753%22%20id%3D%22toc-hId-471052782%22%20id%3D%22toc-hId-471052782%22%3EI've%20tried%20the%20following%20Averageif%20formula-%3C%2FH1%3E%3CH1%20id%3D%22toc-hId--1336401710%22%20id%3D%22toc-hId--1336401681%22%20id%3D%22toc-hId--1336401681%22%3E%3DAVERAGEIFS(E31%3AG31%2CI31%3AK31%2CM31%3AO31%2CQ31%3AS31%2C%22%26lt%3B%26gt%3B%22%22%22)%3C%2FH1%3E%3CH1%20id%3D%22toc-hId-1151111123%22%20id%3D%22toc-hId-1151111152%22%20id%3D%22toc-hId-1151111152%22%3Eto%20exclude%20zeros%20%26amp%3B%20blanks%20for%20non-contiguous%20range%20across%20the%20same%20row%20but%20different%20columns.%20Using%20this%20formula%20I%20get%20a%20%22spill%22%20error.%20I've%20tried%20other%20formulas%20and%20I%20get%20%22Value%22%20error.%20Can%20someone%20help%20me%20with%20the%20correct%20formula%3F%20I'm%20not%20all%20that%20experienced...I've%20also%20attached%20a%20screenshot%20of%20my%20problem.%20Thank%20you%20in%20advance.%26nbsp%3B%3C%2FH1%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1452387%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1452397%22%20slang%3D%22en-US%22%3ERe%3A%20AverageIf%20Question%20--%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1452397%22%20slang%3D%22en-US%22%3EHello%2C%3CBR%20%2F%3E%3CBR%20%2F%3EYou%20can%20read%20the%20article%20below%20to%20have%20a%20better%20understanding%20of%20how%20AVERAGEIFS%20function%20works%20with%20example..%3CBR%20%2F%3E%3CBR%20%2F%3ECheers%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

I've tried the following Averageif formula-

=AVERAGEIFS(E31:G31,I31:K31,M31:O31,Q31:S31,"<>""")

to exclude zeros & blanks for non-contiguous range across the same row but different columns. Using this formula I get a "spill" error. I've tried other formulas and I get "Value" error. Can someone help me with the correct formula? I'm not all that experienced...I've also attached a screenshot of my problem. Thank you in advance. 

2 Replies
Highlighted
Hello,

You can read the article below to have a better understanding of how AVERAGEIFS function works with example..

Cheers
Highlighted

@g8tormjk 

 

Why are you using non-contiguous ranges? Any non-numerical data (e.g. the +/- entries in D31, H31, L31, etc.) will be ignored by AVERAGEIF, so you can use simply:

 

=AVERAGEIF(E31:S31,"<>")

 

Regards