Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates and arrays
In my array I have several conditions. I would like one of those conditions
to look at a date (in another column; 01-sep-2006) and select those in a specific year (i.e. 2006. {=AVERAGE(IF(($A$5:$A$113=Q7)*($K$5:$K$1130)*($M$ 5:$M$1130)*($O$5:$O$113=0)*($O$5:$O$113= ),$O$5:$O$113))} |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates and arrays
You would use something like this:
(YEAR(range)=2006) I notice your formula is referencing the same range 3 times. ($O$5:$O$113=0) ($O$5:$O$113= ) $O$5:$O$113) So, you want to test this range for the year *and* you want this range to be the values that are averaged. So, I can only assume you want the average DATE. If that's the case then you can eliminate this test: ($O$5:$O$113=0) Since testing for the year renders that test superfluous. -- Biff Microsoft Excel MVP "PAL" wrote in message ... In my array I have several conditions. I would like one of those conditions to look at a date (in another column; 01-sep-2006) and select those in a specific year (i.e. 2006. {=AVERAGE(IF(($A$5:$A$113=Q7)*($K$5:$K$1130)*($M$ 5:$M$1130)*($O$5:$O$113=0)*($O$5:$O$113= ),$O$5:$O$113))} |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates and arrays
Hi and thanks for the guidance. I am actually use the last condition to get
the average of numbers in another column. That is why I can't get rid of it. I tried your suggestion two ways, using "2006" and referencing a cell with 2006 in it. Came up with a #NUM! Here it is: {=AVERAGE(IF(($A$5:$A$113=Q7)*($K$5:$K$1130)*($M$ 5:$M$1130)*($O$5:$O$113=0)*(YEAR($O$5:$O$113)=AB 1),$O$5:$O$113))} "T. Valko" wrote: You would use something like this: (YEAR(range)=2006) I notice your formula is referencing the same range 3 times. ($O$5:$O$113=0) ($O$5:$O$113= ) $O$5:$O$113) So, you want to test this range for the year *and* you want this range to be the values that are averaged. So, I can only assume you want the average DATE. If that's the case then you can eliminate this test: ($O$5:$O$113=0) Since testing for the year renders that test superfluous. -- Biff Microsoft Excel MVP "PAL" wrote in message ... In my array I have several conditions. I would like one of those conditions to look at a date (in another column; 01-sep-2006) and select those in a specific year (i.e. 2006. {=AVERAGE(IF(($A$5:$A$113=Q7)*($K$5:$K$1130)*($M$ 5:$M$1130)*($O$5:$O$113=0)*($O$5:$O$113= ),$O$5:$O$113))} |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates and arrays
What do you have in O5:O113? Your formula is attempting to average the
values in O5:O113. -- Biff Microsoft Excel MVP "PAL" wrote in message ... Hi and thanks for the guidance. I am actually use the last condition to get the average of numbers in another column. That is why I can't get rid of it. I tried your suggestion two ways, using "2006" and referencing a cell with 2006 in it. Came up with a #NUM! Here it is: {=AVERAGE(IF(($A$5:$A$113=Q7)*($K$5:$K$1130)*($M$ 5:$M$1130)*($O$5:$O$113=0)*(YEAR($O$5:$O$113)=AB 1),$O$5:$O$113))} "T. Valko" wrote: You would use something like this: (YEAR(range)=2006) I notice your formula is referencing the same range 3 times. ($O$5:$O$113=0) ($O$5:$O$113= ) $O$5:$O$113) So, you want to test this range for the year *and* you want this range to be the values that are averaged. So, I can only assume you want the average DATE. If that's the case then you can eliminate this test: ($O$5:$O$113=0) Since testing for the year renders that test superfluous. -- Biff Microsoft Excel MVP "PAL" wrote in message ... In my array I have several conditions. I would like one of those conditions to look at a date (in another column; 01-sep-2006) and select those in a specific year (i.e. 2006. {=AVERAGE(IF(($A$5:$A$113=Q7)*($K$5:$K$1130)*($M$ 5:$M$1130)*($O$5:$O$113=0)*($O$5:$O$113= ),$O$5:$O$113))} |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates and arrays
The value is the difference between 2 dates (see item Conditional 4 below)
Conditiona1 1 is searching for a specific name. 2. is making sure there is a value for a date 3. is making sure there is a value for another date 4. is making sure the value is positive (data quality) 5. The intent is to pick out the values that occur in a given year, based on #2. Hope that helps. "T. Valko" wrote: What do you have in O5:O113? Your formula is attempting to average the values in O5:O113. -- Biff Microsoft Excel MVP "PAL" wrote in message ... Hi and thanks for the guidance. I am actually use the last condition to get the average of numbers in another column. That is why I can't get rid of it. I tried your suggestion two ways, using "2006" and referencing a cell with 2006 in it. Came up with a #NUM! Here it is: {=AVERAGE(IF(($A$5:$A$113=Q7)*($K$5:$K$1130)*($M$ 5:$M$1130)*($O$5:$O$113=0)*(YEAR($O$5:$O$113)=AB 1),$O$5:$O$113))} "T. Valko" wrote: You would use something like this: (YEAR(range)=2006) I notice your formula is referencing the same range 3 times. ($O$5:$O$113=0) ($O$5:$O$113= ) $O$5:$O$113) So, you want to test this range for the year *and* you want this range to be the values that are averaged. So, I can only assume you want the average DATE. If that's the case then you can eliminate this test: ($O$5:$O$113=0) Since testing for the year renders that test superfluous. -- Biff Microsoft Excel MVP "PAL" wrote in message ... In my array I have several conditions. I would like one of those conditions to look at a date (in another column; 01-sep-2006) and select those in a specific year (i.e. 2006. {=AVERAGE(IF(($A$5:$A$113=Q7)*($K$5:$K$1130)*($M$ 5:$M$1130)*($O$5:$O$113=0)*($O$5:$O$113= ),$O$5:$O$113))} |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates and arrays
4. is making sure the value is positive (data quality)
You're also testing the same range as #4 for the year. If there are negative values in that range then you'll get the #NUM! error. I think you're testing the wrong range for the year! -- Biff Microsoft Excel MVP "PAL" wrote in message ... The value is the difference between 2 dates (see item Conditional 4 below) Conditiona1 1 is searching for a specific name. 2. is making sure there is a value for a date 3. is making sure there is a value for another date 4. is making sure the value is positive (data quality) 5. The intent is to pick out the values that occur in a given year, based on #2. Hope that helps. "T. Valko" wrote: What do you have in O5:O113? Your formula is attempting to average the values in O5:O113. -- Biff Microsoft Excel MVP "PAL" wrote in message ... Hi and thanks for the guidance. I am actually use the last condition to get the average of numbers in another column. That is why I can't get rid of it. I tried your suggestion two ways, using "2006" and referencing a cell with 2006 in it. Came up with a #NUM! Here it is: {=AVERAGE(IF(($A$5:$A$113=Q7)*($K$5:$K$1130)*($M$ 5:$M$1130)*($O$5:$O$113=0)*(YEAR($O$5:$O$113)=AB 1),$O$5:$O$113))} "T. Valko" wrote: You would use something like this: (YEAR(range)=2006) I notice your formula is referencing the same range 3 times. ($O$5:$O$113=0) ($O$5:$O$113= ) $O$5:$O$113) So, you want to test this range for the year *and* you want this range to be the values that are averaged. So, I can only assume you want the average DATE. If that's the case then you can eliminate this test: ($O$5:$O$113=0) Since testing for the year renders that test superfluous. -- Biff Microsoft Excel MVP "PAL" wrote in message ... In my array I have several conditions. I would like one of those conditions to look at a date (in another column; 01-sep-2006) and select those in a specific year (i.e. 2006. {=AVERAGE(IF(($A$5:$A$113=Q7)*($K$5:$K$1130)*($M$ 5:$M$1130)*($O$5:$O$113=0)*($O$5:$O$113= ),$O$5:$O$113))} |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
two arrays | New Users to Excel | |||
Arrays | Excel Discussion (Misc queries) | |||
Arrays | Excel Discussion (Misc queries) | |||
Arrays | Excel Worksheet Functions | |||
Arrays | Setting up and Configuration of Excel |