|
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. |
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. |
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. |
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. |
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. |
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. |
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. |
| All times are GMT +1. The time now is 09:54 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com