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.

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.

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.

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

--
Biff
Microsoft Excel MVP


"CGDorn" wrote in message
...
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.

Great, I appreciate it

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"CGDorn" wrote in message
...
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.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"CGDorn" wrote in message
...
Great, I appreciate it

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"CGDorn" wrote in message
...
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.