Trying to average based on a condition and treat blanks as 0

[cubmar]

Hi,

I have a worksheet where row 1 contains headings.
Rows 2 to 999 have data.
For each row, columns E to X contain a single numeric rating, 0 through to 5. However, commonly blank is used instead of 0.

In column Z I want to provide the average of the columns E to X for each row.

However, I must include blanks and treat them as 0.
and I only want to include columns where the heading (row 1) is not "?"
and if column B for the row is blank, forget the average and show blank

I know this will produce the average of the columns E to X without "?" as a heading:
=IF($B2="","",AVERAGEIF(E$1:X$1,"<>?",E2:X2))

However, it will give a #DIV/0! for any blanks

I know this will produce the average of columns E to X treating BLANK as 0:
=IF($B2="","",AVERAGE(IF(ISNUMBER(E2:X2),E2:X2,IF(E2:X2="",0))))

However, it will include ALL columns regardless of the value in row 1.


How can I combine these, please?

Thanks

[NBVC]

Try:

=IF($B2="","",AVERAGE(IF(E$1:X$1<>"?",E2:X2)))

confirmed with CTRL+SHIFT+ENTER

[cubmar]

Thank you - that worked perfectly!

Try:

=IF($B2="","",AVERAGE(IF(E$1:X$1<>"?",E2:X2)))

confirmed with CTRL+SHIFT+ENTER