averaging % in one cell if another cell is not blank.

[cybilistic]

Hello excel gurus,

I've got a sheet that I need to get the average % from in (column R). Obviously, I could easily do this: =AVERAGE(R29:R35). However, I need to also check another column (column J of the same rows) to see if there is any data in the cell. If the cell in column J is blank, I do not want the average calculated on the cells in column R.

Other info:
The data in column J is text and it varies.
I do need to include zeros in column R in my average.
There are no negative numbers.

Hope this is enough info - thank you

 

[dermie_72]

you could try:
=SUMIF(J:J,"<>",R:R)/COUNTA(J:J)

or select the ranges in the formula

=SUMIF(J29:J35,"<>",R29:R35)/COUNTA(J29:J35)

 

[Brian from Maui]

If you're using 2007 or newer, use the AVERAGEIF function. If 2003 or older try

=AVERAGE(IF(J29:J35<>"",R29:R35))

Enter using Ctrl+Shift+Enter

 

[cybilistic]

Awesome, thanks so much dermie_72 and Brian from Maui!!