Hidden Rows & Arrays

[Guest]

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.

 

[T. Valko]

Are the rows hidden or are they filtered (using DATA>FILTER)?

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=""))

 

[Guest]

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]

Since I don't know how to get a conditional average using SUBTOTAL directly
we have to take the roundabout way. An average is simply a sum divided by a
count. The first part of the formula gets the sum and the second part gets
the count.

The first part of the formula gets a conditional sum based on the cells in
column B being blank. The second part of the formula gets a conditional
count of cells in column A based on cells in column B being blank. So the
conditional average is those 2 numbers divided.

See this for a thorough explanation of SUMPRODUCT and the use of "--":

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Guest]

Great, I appreciate it

Since I don't know how to get a conditional average using SUBTOTAL directly
we have to take the roundabout way. An average is simply a sum divided by a
count. The first part of the formula gets the sum and the second part gets
the count.

The first part of the formula gets a conditional sum based on the cells in
column B being blank. The second part of the formula gets a conditional
count of cells in column A based on cells in column B being blank. So the
conditional average is those 2 numbers divided.

See this for a thorough explanation of SUMPRODUCT and the use of "--":

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[T. Valko]

You're welcome. Thanks for the feedback!