How to get an AVERAGE when some cells have NO VALUE

[mantis]

Hi, I'm hoping someone can help!

I have a spreadsheet that uses an API to gather data from another program and then returns a value into the same cell the API formula is in.

I have 10 of these formulas from A1 to A10. I need an average of these values so i use "=AVERAGE(A1:A10)"

This works great once all 10 cells have a value but the API gathers the info due to specific things happening on the other program so A1 will get a value, then A2 could get a value an hour later, A3 ten minutes after that and so on.

HOW DO I GET EXCEL TO GIVE ME THE AVERAGE OF THE CELLS THAT HAVE A VALUE AND IGNORE THE ONES THAT DONT??????? So it'll give me the average of the first 2 cells, then the first 3 an so on until all 10 have a value.

Many thanks for taking the time to read my problem.

Mantis.

 

[barry houdini]

Hello mantis, welcome to MrExcel,

AVERAGE function ignores blanks so if the cells without a value are blank =AVERAGE(A1:A10) should still work, if only A1 and A2 have numbers and the others are blank then that formula will just average those two.

If you want to ignore zeroes try

=SUMIF(A1:A10,">0")/MAX(1,COUNTIF(A1:A10,">0"))

 

[mantis]

Thanks for such a quick response Barry. Your formula works perfectly.
Regards,
Mantis

 

[kevinwalker]

=SUM(C5:C14)/COUNTIF(C5:C14,"<>0")
this is a sample formula that calculated the average of number greater than or less than zero