Complex Conditional

[PAL]

The formula below works well, but I would like to add 2 modifications.

={MEDIAN(IF((Worksheet!C3:C1968=Aggregate!A5)*(Worksheet!H3:H1968>0),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<=2008)...

Thanks.

 

[T. Valko]

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!A5)*(Worksheet!H3:H1968>0),Worksheet!H3:H1968)),"")

In other versions:

=IF(SUMPRODUCT((Worksheet!C3:C1968=Aggregate!A5)*(Worksheet!H3:H1968>0)),MEDIAN(IF((Worksheet!C3:C1968=Aggregate!A5)*(Worksheet!H3:H1968>0),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)*(Worksheet!H3:H1968>0),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(.....

 

[PAL]

Getting closer, haven't tried the error language yet but. May scream.

This works.

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

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

=MEDIAN(IF((Worksheet!C2:C1967=A4)*(Worksheet!H2:H1967>0)*(YEAR(Worksheet!G2:G1967)=2005+{0,1,2}),Worksheet!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$1967>0),Worksheet!$H$2:$H$1967,""))

What the heckis going on.

 

[T. Valko]

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

 

[PAL]

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:H1967>0)*(Worksheet!H2:H1967<>"")*(YEAR(Worksheet!G2:G1967)=2005+{0,1,2}),Worksheet!H2:H1967,""))

 

[T. Valko]

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?

 

[PAL]

What is in: (text, numbers, formula blanks, all of these)
Worksheet!C2:C1967> A4 -- Text, being referenced from elsewhere.

Worksheet!H2:H1967 is a formated as a number. It the difference between 2
dates (cyle times) and blank if one of the dates is missing. I test for
negative numbers, because there is some rows w/ bad data, ie the dates are
backwards....

Worksheet!G2:G1967: Is one of the dates used in "H" originating elsewhere.

Bottom line - trying to calculate the median values for cycle times in a
given time range (2005-2007).

 

[PAL]

When I use error checking functionality it that it doesn't like
((Worksheet!C2:C1967=A4) how I reference A4. I even try putting in the
worksheet name to no avail.

 

[T. Valko]

Try this:

Just add your sheet name and the full ranges.

Array entered:

=MEDIAN(IF((C2:C10=A4)*(YEAR(G2:G10)=2005+{0,1,2})*(H2:H10<>"")*(H2:H10>0),H2:H10))

If this doesn't work I'm out of ideas and would need to see the data to find
out what the problem is.

 

[PAL]

Excel Wins!!!!!!!!!

Seems like whenever I put the date range functionality in it either screws
it up, or ignores that parameter.

I appreciate your help on this one.

 

[PAL]

Got it!

The solution is:

=MEDIAN(IF((Worksheet!$D$2:$D$1967=M4)*(Worksheet!$I$2:$I$1967>0)*(Worksheet!$G$2:$G$1967>=$U$2)*(Worksheet!$G$2:$G$1967<$U$3),Worksheet!$I$2:$I$1967,""))


where I reference the through another cell.

For some reason the count needs uses a different formula....

=COUNT(IF((Worksheet!D21967=M4)*(Worksheet!I2:I1967>0)*(YEAR(Worksheet!G2:G1967)=2008+{0,1,2}),Worksheet!I2:I1967,""))

Don't know why, don't care...maybe one day I will try to have the count work
the way the median does........later.................