What's in Worksheet!H2:H1967 ?

If there is text or formula blanks in that range that will also cause a
problem.

In Excel a text entry will *always* evaluate to be greater than any number.
This is "dumb" but that's how the programmers did it!

So: text 0 = TRUE. A formula blank ("") is a TEXT string. So: "" 0 = TRUE.

The YEAR function expects a DATE VALUE as an argument. A text value causes
the #VALUE! error:

YEAR("") = #VALUE!

Tell me *exactly* what you have in each range being referenced in your
formula:

What is in: (text, numbers, formula blanks, all of these)

Worksheet!C2:C1967
A4
Worksheet!H2:H1967
Worksheet!G2:G1967

Why are you testing the range for 0, are there negative numbers in that
range?


--
Biff
Microsoft Excel MVP


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