| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional
I am working on a big array formula that currently works great.
=AVERAGE(IF(('Export and Site View'!$M$2:$M$282=$K$4)*('Export and Site View'!$C$2:$C$282=$A$2)*('Export and Site View'!$H$2:$H$282="Active")*('Export and Site View'!$N$2:$N$2820),('Export and Site View'!$N$2:$N$282),"")) The first condition = $K$4 references a field with a year (ie. 2007). How can I change the condition to reference 2006 and 2007. I am looking to return a values that look up two years. Do I have to add an OR Condition? If so where? Thanks. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional
Change
=AVERAGE(IF((('Export and Site View'!$M$2:$M$282=$K$4)+('Export and Site View'!$M$2:$M$282=$K$3))*('Export and Site View'!$C$2:$C$282=$A$2)*('Export and Site View'!$H$2:$H$282="Active")*('Export and Site View'!$N$2:$N$2820),('Export and Site View'!$N$2:$N$282),"")) Enter 2006 into cell K3, and 2007 into K4. HTH, Bernie MS Excel MVP "PAL" wrote in message ... I am working on a big array formula that currently works great. =AVERAGE(IF(('Export and Site View'!$M$2:$M$282=$K$4)*('Export and Site View'!$C$2:$C$282=$A$2)*('Export and Site View'!$H$2:$H$282="Active")*('Export and Site View'!$N$2:$N$2820),('Export and Site View'!$N$2:$N$282),"")) The first condition = $K$4 references a field with a year (ie. 2007). How can I change the condition to reference 2006 and 2007. I am looking to return a values that look up two years. Do I have to add an OR Condition? If so where? Thanks. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional
Try this:
K4 = 2006 =AVERAGE(IF(('Export and Site View'!$M$2:$M$282=$K$4+{0,1})....... -- Biff Microsoft Excel MVP "PAL" wrote in message ... I am working on a big array formula that currently works great. =AVERAGE(IF(('Export and Site View'!$M$2:$M$282=$K$4)*('Export and Site View'!$C$2:$C$282=$A$2)*('Export and Site View'!$H$2:$H$282="Active")*('Export and Site View'!$N$2:$N$2820),('Export and Site View'!$N$2:$N$282),"")) The first condition = $K$4 references a field with a year (ie. 2007). How can I change the condition to reference 2006 and 2007. I am looking to return a values that look up two years. Do I have to add an OR Condition? If so where? Thanks. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional
PAL,
I should have noted that in Array formulas, Multiplication is the equivalent of logical AND, Addition (within parens) is the equivalent of logical OR. HTH, Bernie MS Excel MVP "PAL" wrote in message ... I am working on a big array formula that currently works great. =AVERAGE(IF(('Export and Site View'!$M$2:$M$282=$K$4)*('Export and Site View'!$C$2:$C$282=$A$2)*('Export and Site View'!$H$2:$H$282="Active")*('Export and Site View'!$N$2:$N$2820),('Export and Site View'!$N$2:$N$282),"")) The first condition = $K$4 references a field with a year (ie. 2007). How can I change the condition to reference 2006 and 2007. I am looking to return a values that look up two years. Do I have to add an OR Condition? If so where? Thanks. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional
Not sure why, but that counts 2006 values twice when averaging.....
Bernie MS Excel MVP "T. Valko" wrote in message ... Try this: K4 = 2006 =AVERAGE(IF(('Export and Site View'!$M$2:$M$282=$K$4+{0,1})....... -- Biff Microsoft Excel MVP "PAL" wrote in message ... I am working on a big array formula that currently works great. =AVERAGE(IF(('Export and Site View'!$M$2:$M$282=$K$4)*('Export and Site View'!$C$2:$C$282=$A$2)*('Export and Site View'!$H$2:$H$282="Active")*('Export and Site View'!$N$2:$N$2820),('Export and Site View'!$N$2:$N$282),"")) The first condition = $K$4 references a field with a year (ie. 2007). How can I change the condition to reference 2006 and 2007. I am looking to return a values that look up two years. Do I have to add an OR Condition? If so where? Thanks. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional
Hmmm...
Works in all my tests: http://img503.imageshack.us/img503/7786/averagenr1.jpg -- Biff Microsoft Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Not sure why, but that counts 2006 values twice when averaging..... Bernie MS Excel MVP "T. Valko" wrote in message ... Try this: K4 = 2006 =AVERAGE(IF(('Export and Site View'!$M$2:$M$282=$K$4+{0,1})....... -- Biff Microsoft Excel MVP "PAL" wrote in message ... I am working on a big array formula that currently works great. =AVERAGE(IF(('Export and Site View'!$M$2:$M$282=$K$4)*('Export and Site View'!$C$2:$C$282=$A$2)*('Export and Site View'!$H$2:$H$282="Active")*('Export and Site View'!$N$2:$N$2820),('Export and Site View'!$N$2:$N$282),"")) The first condition = $K$4 references a field with a year (ie. 2007). How can I change the condition to reference 2006 and 2007. I am looking to return a values that look up two years. Do I have to add an OR Condition? If so where? Thanks. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional
Of course, now it works - I deleted the workbook with the weird results, and cannot replicate it. Sorry, Bernie MS Excel MVP "T. Valko" wrote in message ... Hmmm... Works in all my tests: http://img503.imageshack.us/img503/7786/averagenr1.jpg -- Biff Microsoft Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Not sure why, but that counts 2006 values twice when averaging..... Bernie MS Excel MVP "T. Valko" wrote in message ... Try this: K4 = 2006 =AVERAGE(IF(('Export and Site View'!$M$2:$M$282=$K$4+{0,1})....... -- Biff Microsoft Excel MVP "PAL" wrote in message ... I am working on a big array formula that currently works great. =AVERAGE(IF(('Export and Site View'!$M$2:$M$282=$K$4)*('Export and Site View'!$C$2:$C$282=$A$2)*('Export and Site View'!$H$2:$H$282="Active")*('Export and Site View'!$N$2:$N$2820),('Export and Site View'!$N$2:$N$282),"")) The first condition = $K$4 references a field with a year (ie. 2007). How can I change the condition to reference 2006 and 2007. I am looking to return a values that look up two years. Do I have to add an OR Condition? If so where? Thanks. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| conditional sum ?? | Excel Worksheet Functions | |||
| Conditional Rank (or rather, Conditional Range) | Excel Worksheet Functions | |||
| Is IF() Conditional the way to do this? | Excel Worksheet Functions | |||
| Conditional Formatting that will display conditional data | Excel Worksheet Functions | |||
| Conditional sum | Excel Worksheet Functions | |||
Linear Mode
