When using AVERAGE function, ignore cells with certain values

Hi there, I'm a newbie at this and hoping for some quick help. I guess my proficiency with excel is considered basic - it's kind of relative though I suppose! I'm hoping to become something of an expert within the next couple of months.

I am trying to determine how I can use the AVERAGE function but have it ignore cells with certain values. Below I have my 8 rows with 11 variables averaged at the end of each row using the AVERAGE(cell1:cell2) function. I need to exclude U, N, and blank values from the calculation.

I suspect this is a really easy one for most out there. Help, anyone?


0 1 0 0 0 0 0 0 0 0 1 1.818181818
1 1 1 1 0 1 1 0 2 1 2 10
0 3 2 U 0 0 U U 3 U 1 12.85714286
0 1 U U 1 1 U U 2 U U 10
0 0 0 U 0 0 0 U 1 1 0 2.222222222
0 3 1 0 0 0 2 3 3 2 0 12.72727273
1 3 2 1 1 2 2 1 2 2 2 17.27272727
3 3 1 3 2 1 1 U 2 3 1 20

I think you have multiplied your averages by 10, but anyway, you can use this formula:

=AVERAGEIFS(A1:K1,A1:K1,">0")

then copy down.

Hope this helps.

Pete

I did multiply by 10 - thanks Pete!

I also realized that it looks like the formula ignores any non-numerical values anyway. So it is automatically ignoring the U values.

Thanks again!

Yes, it will ignore the letters, but it will count zeros (not the same as blanks), so on your top row you have a total of 2 in 2 cells (real average = 1, or 10 using your *10). The formula corrects for this.

You could also use:

=AVERAGEIF(A1:K1,">0")

Hope this helps.

Pete

Thanks. I actually want to include zeros in my average in this case.

It helped me as well. Thanks