Posted to microsoft.public.excel.misc
|
|
Hidden Rows & Arrays
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.
|