This works well, Thanks. If possible could you walk me through the workings
of the formula. I don't understand what you are telling excel to do,
particularly the "--(B5:B20="")" I have never seen -- entered into a formula.

Thanks again.

"T. Valko" wrote:

Are the rows hidden or are they filtered (using DATAFILTER)?

This works on FILTERED rows:

=SUMPRODUCT(SUBTOTAL(9,OFFSET(A5:A20,ROW(A5:A20)-ROW(A5),0,1)),--(B5:B20=""))/SUMPRODUCT(SUBTOTAL(2,OFFSET(A5:A20,ROW(A5:A20)-ROW(A5),0,1)),--(B5:B20=""))

--
Biff
Microsoft Excel MVP


"CGDorn" wrote in message
...
I have used the following formula for two columns of data:
{=average(if(B1:B50="",A1:A50,"NA"))}
This works well for me to calculate the average of A1:A50 where I put a *
in
the B column to exclude some data. I do the same thing for the median
calculation as well.

My question is: Is there a way to use this same type of format to
calculate
the average if I am filtering a column so that some rows are excluded?

I have tried: {=subtotal(109,if(B1:B50="",A1:A50,"NA"))} and it does not
seem to work. Any suggestions appreciated.