I'm working with an dynamically defined, continous block of cells. The
requestor doesn't want to weed out or filter out unwanted records,
they fit a criteria. So I will have to use the domain aggregate
functions DAVERAGE, DSTDEV etc... However, there is no DMEDIAN. Any
tips to creating my own Dfunction?

Thanks Muchly,

Ananda

=Median(if(A1:A20="AA",B1:B20))
entered with Ctrl+shift+enter rather than just enter since this is an array
formula

column A contains the conditional data and column B the numbers to use for
the median

=Median(if((A1:A20="AA")*(C1:C20="CC"),B1:B20))
entered with Ctrl+shift+enter rather than just enter since this is an array
formula

shows how to have conditions in Column A and C

--
Regards,
Tom Ogilvy


"AnandaSim" wrote in message
oups.com...
I'm working with an dynamically defined, continous block of cells. The
requestor doesn't want to weed out or filter out unwanted records,
they fit a criteria. So I will have to use the domain aggregate
functions DAVERAGE, DSTDEV etc... However, there is no DMEDIAN. Any
tips to creating my own Dfunction?

Thanks Muchly,

Ananda

Thanks very much Tom. I was just thinking of this since in the same
project in another worksheet, I had come up with that for other
aggregate measures. However, it has been two years and I almost forgot
this.

Given that I have to use D functions for average, max, std dev, do you
think I would be better off with the array formulae?

Thanks

Ananda


On Feb 20, 3:25 pm, "Tom Ogilvy" wrote:
=Median(if(A1:A20="AA",B1:B20))
entered with Ctrl+shift+enter rather than just enter since this is an array
formula

column A contains the conditional data and column B the numbers to use for
the median

=Median(if((A1:A20="AA")*(C1:C20="CC"),B1:B20))
entered with Ctrl+shift+enter rather than just enter since this is an array
formula

shows how to have conditions in Column A and C

--
Regards,
Tom Ogilvy

D functions are much faster - if you have already made provision for using
the separate criteria range, then I think it would be personal choice on
which you use.

--
Regards,
Tom Ogilvy


"AnandaSim" wrote:

Thanks very much Tom. I was just thinking of this since in the same
project in another worksheet, I had come up with that for other
aggregate measures. However, it has been two years and I almost forgot
this.

Given that I have to use D functions for average, max, std dev, do you
think I would be better off with the array formulae?

Thanks

Ananda


On Feb 20, 3:25 pm, "Tom Ogilvy" wrote:
=Median(if(A1:A20="AA",B1:B20))
entered with Ctrl+shift+enter rather than just enter since this is an array
formula

column A contains the conditional data and column B the numbers to use for
the median

=Median(if((A1:A20="AA")*(C1:C20="CC"),B1:B20))
entered with Ctrl+shift+enter rather than just enter since this is an array
formula

shows how to have conditions in Column A and C

--
Regards,
Tom Ogilvy

Thanks Tom.

Ananda

On Feb 21, 12:33 am, Tom Ogilvy
wrote:
D functions are much faster - if you have already made provision for using
the separate criteria range, then I think it would be personal choice on
which you use.

--
Regards,
Tom Ogilvy

Hi Tom,

On Feb 20, 3:25 pm, "Tom Ogilvy" wrote:

=Median(if(A1:A20="AA",B1:B20))

I've been using and IFless approach for the aggregate functions. For
example

=AVERAGEA((STATUS="ENROLLED")*TEST1)

where STATUS is a named range
where TEST1 is a named range
The two named ranges are identical in block height and single column

Seems to work. Any fallacy in logic here?

Thanks

Ananda

Possibly - depends on what you want. You would including zero values for
cells that do not equal enrolled in your average which I wouldn't think you
would want.

also averageA treats non-numeric values as having a value of zero. Average
doesn't.

--
Regards,
Tom Ogilvy

"AnandaSim" wrote in message
oups.com...
Hi Tom,

On Feb 20, 3:25 pm, "Tom Ogilvy" wrote:

=Median(if(A1:A20="AA",B1:B20))

I've been using and IFless approach for the aggregate functions. For
example

=AVERAGEA((STATUS="ENROLLED")*TEST1)

where STATUS is a named range
where TEST1 is a named range
The two named ranges are identical in block height and single column

Seems to work. Any fallacy in logic here?

Thanks

Ananda

Thanks Tom, I've got a new wrinkle:

Student Status Marks
Joe ENROLLED 20
George NOT 10
Dick ENROLLED DNS
Alan ENROLLED 10

=AVERAGEA(IF(Status="ENROLLED",Marks))

as an array formula works fine - it handles the "DNS" as a zero.

=STDEVA(IF(Status="ENROLLED",Marks))

fails. It does not count the DNS as a zero. It acts like STDEV.
If I replace the cell containing DNS with zero, then it calculates
differently.

Any wisdom appreciated.

Thanks

I couldn't reproduce a difference in behavior:

STDEVA STDEV
5 5
7 7
3 3
8 8
DNS 0
9 9
2 2
DNS 0
9 9
9 9
10 10
10 10
8 8

3.71587033 3.71587033



It appeared to treat DNS as zero as specified in the help

in the formula bar, highlight this much of your formula

IF(Status="ENROLLED",Marks)

and Hit F9 to evaluate it and see what array it is returning? Note that
IF(Status="ENROLLED",Marks) will return false for "NOT" and both AverageA
and STDEVA would treat that as zero, but I believe I already pointed that
out for AverageA.

--
regards,
Tom Ogilvy


"AnandaSim" wrote in message
ps.com...
Thanks Tom, I've got a new wrinkle:

Student Status Marks
Joe ENROLLED 20
George NOT 10
Dick ENROLLED DNS
Alan ENROLLED 10

=AVERAGEA(IF(Status="ENROLLED",Marks))

as an array formula works fine - it handles the "DNS" as a zero.

=STDEVA(IF(Status="ENROLLED",Marks))

fails. It does not count the DNS as a zero. It acts like STDEV.
If I replace the cell containing DNS with zero, then it calculates
differently.

Any wisdom appreciated.

Thanks