error (#DIV/0) AVERAGEIFS formula Solved

Question

Hello everyone,
I am looking to correct the AVERAGEIFS formula to calculate the average of a range while ignoring empty cells that result in the error (#DIV/0) in Excel 2013.
For example, attached file https://www.cjoint.com/c/GDsrlw2cYFX
Thank you in advance for your help.

eriiic · Answer

Hello,

it's not that there are empty cells, but that all of them are empty. It cannot calculate.
Put at least one value (per criterion ok), or use =iferror(your_formula,"")
eric

By continually trying, we eventually succeed.
So the more it fails, the more chances we have that it works. (the Shadoks)
In addition to thank you (yes, it does happen!!!), remember to mark it as resolved. Thank you

Vaucluse · Answer

Hello (and good evening eriiic)

It is not clear how you can calculate an average with this formula by looking for the values of H in column B, as they are only represented on a single line
For example, place this formula in M19:
=COUNTIF(B3:B35066, H19)
and you will see that it returns 1, because the increment of column B changes the displayed values by day:
to understand better: place the cursor in B19 and then in B43 ^ then in B67 and compare the two 16:00
or more completely,
either temporarily format column B as standard and you will see that the same times do not have the same numerical value.
or format the column as custom and [hh]:mm:ss with the HH in brackets and you will see the differences

If you want to calculate the average with this list, two options depending on what you want to do:

this formula in M19 for example:

=SUMPRODUCT((B3:B35066-INT(B3:B35066)=H19)*(E3:E35066))/SUMPRODUCT((B3:B35066-INT(B3:B35066)=H19)*1)
which will calculate the average with all the rows containing H19, including the blanks in E

or
=SUMPRODUCT((B3:B35066-INT(B3:B35066)=H19)*(E3:E35066))/SUMPRODUCT((B3:B35066-INT(B3:B35066)=H19)*(E3:E35066<>""))

which will calculate the average only with the number of rows with a non-empty E

If on the other hand, that is what you want, why calculate an average on a single line?

best regards

The quality of the answer mainly depends on the clarity of the question, thank you!

Error (#DIV/0) AVERAGEIFS formula

2 answers

How to delete quarantine files in defender?

Unable to type the at-sign

Https://www.youtube.com/@topblazo

Reinstallation of w11 impossible after pc crash...

Download impossible for visual petanque

Connect external dvd player to samsung tablet

Phone not receiving certain sms

Images from videos - retrieval

Chkdsk /f and sfc /scannow okay, but startup impossible.

Recover deleted messages