average formula without using cells that have "empty" formulas

Sorry for my lack of correct terminology and perhaps confusing question. I'm very new to excel.

In the N column, I have a formula to calculate the average of cells H11-M11. In cells K11, L11, and M11, I have formulas that pull data from other excel sheets. Currently, no data has been inputted. Is there a formula that I can use that will not include the “empty” formulas in the overall average from cells H11-M11 but will include the formula cells once data has been inputted?

So, if K11 has a formula that turns to data, how can I include it in the overall average and continue to not include L11 and M11….and then include L11 once the formula turns to data and continue to not include M11….etc.?


Thanks for all your help!! =)

If your formulas produce a numeric result (including 0) in K11, L11 or M11 they will be used in the average. If they don't produce a numeric result (""), the AVERAGE function in Excel ignores those cells towards the average.

- Moo

=SUM(H11:M11)/COUNTIF(H11:M11,">0")
Formula will not include empty cells and take average only cells that greater than 0

Ok, I've been playing around with some IF formulas, but still turning up nothing.

=IF(K11="",AVERAGE(H11:K11),AVERAGE(H11:J11))

That formula works only if K11 has a number replacing the #DIV/O!. But, it doesn't help me get the AVERAGE of H11:M11 because L11 and M11 still have #DIV/0! in the cells. I guess I don't know what to type into the formula to represent a formula (that doesn't have data being plugged into it) or #DIV/0!. Also, I don't know how to create an IF, when a previous IF has been fulfilled.

Thanks for the formula, eisayew, but it did not work. =(

Unfortunately, I can't upload a sample workbook due to privacy issues. However, I'm pretty sure that I have figured out the formula! As of right now, the following formula seems to be doing wonders.

=AVERAGEIFS(H11:M11,H11:M11,">0")

Thanks to all who helped give me hints and clues. =D