|
Complex Conditional
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. |
Complex Conditional
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. |
Complex Conditional
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. |
Complex Conditional
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. |
Complex Conditional
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. |
Complex Conditional
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. |
Complex Conditional
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). Why are you testing the range for 0, are there negative numbers in that range? "T. Valko" wrote: 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. |
Complex Conditional
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" wrote: 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. |
Complex Conditional
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:H100),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. -- Biff Microsoft Excel MVP "PAL" wrote in message ... 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). Why are you testing the range for 0, are there negative numbers in that range? "T. Valko" wrote: 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. |
Complex Conditional
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" wrote: 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" wrote: 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. |
Complex Conditional
Got it!
The solution is: =MEDIAN(IF((Worksheet!$D$2:$D$1967=M4)*(Worksheet! $I$2:$I$19670)*(Worksheet!$G$2:$G$1967=$U$2)*(Wo rksheet!$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!D2:D1967=M4)*(Worksheet!I2:I1 9670)*(YEAR(Worksheet!G2:G1967)=2008+{0,1,2}),Wor ksheet!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................. "PAL" wrote: 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" wrote: 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. |
| All times are GMT +1. The time now is 04:00 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com