Yes I do have blanks still,

I went back and added condition to account for this and got the same result

=MEDIAN(IF((Worksheet!C2:C1967=A4)*(Worksheet!H2:H 19670)*(Worksheet!H2:H1967<"")*(YEAR(Worksheet!G 2:G1967)=2005+{0,1,2}),Worksheet!H2:H1967,""))

"T. Valko" wrote:

Do you have formula blanks ("") in your date range (as per your other post
from a few days ago) ?

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
Getting closer, haven't tried the error language yet but. May scream.

This works.

=COUNT(IF((Worksheet!C2:C1967=A4)*(Worksheet!H2:H1 9670)*(YEAR(Worksheet!G2:G1967)=2005+{0,1,2}),Wor ksheet!H2:H1967,""))

This doesn't. It comes up with the #Value! error.

=MEDIAN(IF((Worksheet!C2:C1967=A4)*(Worksheet!H2:H 19670)*(YEAR(Worksheet!G2:G1967)=2005+{0,1,2}),Wo rksheet!H2:H1967,""))


This works but it is considerable different from above Median formula,
including minus the date range.

=MEDIAN(IF((Worksheet!$C$2:$C$1967=Aggregate!A7)*( Worksheet!$H$2:$H$19670),Worksheet!$H$2:$H$1967," "))

What the heckis going on.




"T. Valko" wrote:

1. IF the value is blank how do I modify so it leaves the
cell blank instead of the famous, "#num!" error

If you want an error trap built into the formula it'll be pretty long
unless
you're using Excel 2007.

In Excel 2007 only:

=IFERROR(MEDIAN(IF((Worksheet!C3:C1968=Aggregate!A 5)*(Worksheet!H3:H19680),Worksheet!H3:H1968)),"")

In other versions:

=IF(SUMPRODUCT((Worksheet!C3:C1968=Aggregate!A5)*( Worksheet!H3:H19680)),MEDIAN(IF((Worksheet!C3:C19 68=Aggregate!A5)*(Worksheet!H3:H19680),Worksheet! H3:H1968)),"")

It might be better to use another cell with the MEDIAN formula and then
test
that cell to see if it contains a number or an error.

A1:
=MEDIAN(IF((Worksheet!C3:C1968=Aggregate!A5)*(Work sheet!H3:H19680),Worksheet!H3:H1968))

Then:

=IF(COUNT(A1),A1,"")

2. I would like to add a date range, ie. 2005-2008, to the conditions.

Add one of these expressions where needed. They all do the same thing.

....*(YEAR(G3:G1968)=2005+{0,1,2,3})...

....*(YEAR(G3:G1968)={2005,2006,2007,2008})...

....*(YEAR(G3:G1968)=2005)*(YEAR(G3:G1968)<=2008) ...

All of the above fomulas are arrays except for the =IF(COUNT(.....


--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
The formula below works well, but I would like to add 2 modifications.

={MEDIAN(IF((Worksheet!C3:C1968=Aggregate!A5)*(Wor ksheet!H3:H19680),Worksheet!H3:H1968,""))}

1. IF the value is blank how do I modify so it leaves the cell blank
instead of the famous, "#num!" error

2. I would like to add a date range, ie. 2005-2008, to the conditions.
The
current format of the date is, "04-mar-01. This doesn't seem to work.
Iadded this to the above
*(Worksheet!G3:G1968=2005)*(Worksheet!G3:G1968<=2 008)...

Thanks.