| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Complex conditional formatting | Excel Worksheet Functions | |||
| Complex or Conditional Countif(s) | Excel Worksheet Functions | |||
| complex conditional format | Excel Discussion (Misc queries) | |||
| complex?? Q about Conditional formatting | Excel Worksheet Functions | |||
| Complex Conditional formulas | Excel Worksheet Functions | |||
Hybrid Mode
