Average If Function?

[drttjn]

I've got a spreadsheet I need to average data in. I'm using links to pull my data from other sheets to this one. If there is no data, my other sheets display "NoData" in the cells. I did this because some of my data may contain zeros. I need to average the values of the cells that contain actual numerical data. So heres what I've got.

C3:C27 is my data range
C29 is where my formula/average will be displayed

I'm not sure how to average this data, but this is basically what I want.

If the cell contents does not = "NoData", average the values that meet this criteria


Also, seperate question, how do i reference the cell value within the formula? I used to know how to do that, but I've forgotten.
'Value' ?maybe or something similar
cell.contents
#value

 

[Domenic]

The AVERAGE function ignores text values, so you can simply use...

=AVERAGE(C3:C27)

Also, regarding your formulas in C3:C27, instead of returning "NoData" you can actually return an empty/null string (""). So could have something like this...

=IF('Sheet2'!A2<>0,'Sheet2'!A2,"")

Hope this helps!

 

[Chris Macro]

Does this work for you?

=AVERAGEIF(C3:C27," < > NoData")