Formula - Audit data requiring combining averages (but not count some cells)

Hi all,

I am fairly average at using excel and need some suggestions on a formula (example data attached).

I have 5 example questions from an audit (B8:B12) with varying percentages of compliance (C8:E12). The number of audits completed are shown at the bottom of each column (C15:E15). I want to average the months of jan-mar data into the final column (F8:F12). Easy enough. But the formula has to not count any data with N/A or blank cells, and hence not include those audits (at the bottom of the column) into the average.

Also as a preference I would like it if hypothetically jan, feb and march all had N/A or blank cells, that the combined average jan-mar cell would state N/A or blank. But this is not a major issue. It would just mean less editing ultimately. (lots of data and ongoing audits being completed - so it would be good to have a formula that addressed all of this)

Any suggestions would be appreciated. Like I said I am very mediocre at using excel so there could be a relatively simple solution.


Thanks

Legend!! Almost worked. Only issue was that if all three cells were blank it would still state N/A. I re-worked the formula using your idea and came up with:

=IF(OR(ISNUMBER(C8), ISNUMBER(D8), ISNUMBER(E8)), ((IF(ISNUMBER(C8),(C8*C$15),0))+(IF(ISNUMBER(D8), (D8*D$15),0))+(IF(ISNUMBER(E8), (E8*E$15),0)))/(IF(ISNUMBER(C8),C$15,0)+IF(ISNUMBER(D8), D$15, 0)+IF(ISNUMBER(E8), E$15, 0)), (IF(OR(ISTEXT(C8),ISTEXT(D8),ISTEXT(E8)),"N/A", (IF(OR(ISBLANK(C8),ISBLANK(D8),ISBLANK(E8)),"",0)))))

Which does the trick. Basically reworked to solve numbers first then text then blank, rather than text and blank then solve numbers.

Not sure if that made sense but you helped me out heaps.

Cheers mate.