Conditional

P

PAL

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$282>0),('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.
 
B

Bernie Deitrick

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$282>0),('Export and Site
View'!$N$2:$N$282),""))

Enter 2006 into cell K3, and 2007 into K4.

HTH,
Bernie
MS Excel MVP
 
T

T. Valko

Try this:

K4 = 2006

=AVERAGE(IF(('Export and Site View'!$M$2:$M$282=$K$4+{0,1}).......
 
B

Bernie Deitrick

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
 
B

Bernie Deitrick

Not sure why, but that counts 2006 values twice when averaging.....

Bernie
MS Excel MVP
 
B

Bernie Deitrick

Of course, now it works - I deleted the workbook with the weird results, and cannot replicate it.

Sorry,

Bernie
MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top