Average help

[SFCChase]

Is there a way to Average a series of data with a #N/A included in the string?

I have a column of 45 data numbers, 3 or 4 of which have #N/A errors due to information not being available. Is there a way to write a formula that will average the remaining 41 OR 42 items without me having to change the range every time I use equation?

NAME	SCORE		TOTAL AVERAGE
BOB	90		AVERAGE ALL NUMERICAL CELLS B2:B9
JOE	100		SHOULD EQUAL 95
MIKE	#N/A
SUE	100
BILL	95
JEN	85
LOU	#N/A
BETH	100

Any help would be appreciated!

 

[Joe4]

One way:

=SUMIF(B2:B46,">0",B2:B46)/COUNTIF(B2:B9,">0")

assuming all your number are greater than 0.

 

[Logit]

Another formula :

=AVERAGEIF(B2:B9,"<1e100")

 

[SFCChase]

One way:

=SUMIF(B2:B46,">0",B2:B46)/COUNTIF(B2:B9,">0")

assuming all your number are greater than 0.

Thanks! Worked great! I knew I was overthinking it getting set on having to use an AVERAGE function.

 

[Joe4]

Thanks! Worked great! I knew I was overthinking it getting set on having to use an AVERAGE function.

You are welcome.

You can use AVERAGEIF, like logit shows (I keep forgetting about that function!).

If I had used that, I could have simplified the logic I used to:

=AVERAGEIF(B2:B46,">0")