Forum Discussion

Copper Contributor

Oct 20, 2022

Solved

Averageifs function divide by zero error

I trying to get the correct average of just the q1 and q3 values using averageifs function. Have looked a lot online but couldn't find a solution. However, someone kindly did help me here on MS commu...

excel

Formulas and Functions

office 365

JKPieterse
Oct 20, 2022
That is due to the empty cells in your data, to cater for those we need an additional check:
=AVERAGE(IF((H2:H13<>"")*((G2:G13="Q1")+(G2:G13="Q3"))>0,H2:H13,""))

Copper Contributor

Oct 21, 2022

Ok this one works. And I now see why you are not convinced about the divide by two formula. Turns out, it returns the wrong answer if I enter a value in one of the blank cells say 123456 in H3. Just still wish there was a much shorter formula to compute this

JKPieterse

Silver Contributor

Oct 21, 2022

I'm afraid not! There might be alternatives, but I doubt if those will be much simpler. You could create a LAMBDA function for this, but I doubt whether it is worth the effort:
https://jkp-ads.com/Articles/Excel-lambda-function-basics.asp

Hr
Copper Contributor
Oct 21, 2022
Ok will take a look, thanks!