Forum Discussion

g8tormjk's avatar
g8tormjk
Copper Contributor
Jun 10, 2020

AverageIf Question --

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. 

Formulas and Functions

2 Replies

  • Jos_Woolley's avatar
    Jos_Woolley
    Iron Contributor
    Jun 10, 2020

    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

  • Abiola1's avatar
    Abiola1
    MVP
    Jun 10, 2020
    Hello,

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

    Cheers

Resources