calculate average

[Moltke]

Hi everyone! Hope you’re all having a nice life!

I have this sheet with a formula/function to calculate the average =IFERROR(AVERAGE(D5:I5);" ") and it works, to some extent, since some or even all cells may or may not have any value, they might be blank, which generates a problem; the average shown in J5 won’t be as exact. For example, let’s say that in that range of cells D5:I5 only D5 contains a value, unless I’m wrong, I think the result shown in J5 should be the one obtained from the division of that value by the number of cells, in this case 6, however, if the value of D5 is 12 in J5 is shown 12 and not 2, as it should be, then my question is, what formula/function should/can I use to make that happen? I tried =AVERAGEIF but it didn’t work, I probably did it wrong. Thanks in advance for your answers.

[dscheikey]

You can use: =SUMPRODUCT(AVERAGE(IF(D5:I5="";0;D5:I5));1)

[Moltke]

Thanks for you answer, however, that gives #¡DIV/0! error.

[dscheikey]

Ohh sorry. You need to change the semicolons to commas.
=SUMPRODUCT(AVERAGE(IF(D5:I5="",0,D5:I5)),1)

[kompilainenn]

AVERAGE function don’t calculate empty cells or cells with text instead numbers. For your case you should enter zero in all cells without data, if you want get average for all six cells

[Moltke]

Thanks for your answer. I’m not not sure I understand what you’re trying to say, is not possible to do what I want?

[ve3oat]

@kompilainenn is saying that, if you want the empty and blank cells to be counted when calculating the average, then your calculated average will always be wrong unless you enter a zero in each and all of the empty and blank cells.