[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Using MAX function with nested AND statement within an IF statement

Posted on 2011-04-21
4
Medium Priority
?
217 Views
Last Modified: 2012-05-11
I am trying to write a formula in my Excel spreadsheet to perfrom the following:

For a value in column A on Sheet 1, I am trying to look up the corresponding value in a named range (column A on sheet 2) named 'NAME' and return the highest value from named range (column D on sheet 2) named 'DATE' where the parameter in named range (column B on sheet 2) named 'TYPE' is equal to ACTIVE.

Here is what I have so far:
{=MAX(IF(NAME=A2,DATE,0))}

This returns the highest value fromt he DATE range regardless of type.

The change I tried to make is as follows:

{=MAX(IF(AND(NAME=A2,TYPE="ACTIVE"),DATE,0))}

This returns a value of zero rather than the highest value int he DATE range where NAME = A2 and TYPE = ACTIVE.

Can someone help me with what I am doing wrong?
0
Comment
Question by:Hobart007
  • 3
4 Comments
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 2000 total points
ID: 35442791
=MAX(IF((NAME=A2)*(TYPE="ACTIVE")>0,DATE,0))

Kevin
0
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 2000 total points
ID: 35442813
The OR and AND functions do not work inside array formulas unless they reference only single cells and constants. They can be simulated as illustrated below.

To simulate an OR, instead of:

   =AVERAGE(IF(OR(A1:A100>1,B1:B100="A"),C1:C100))

use:

   =AVERAGE(IF((A1:A100>1)+(B1:B100="A")>0,C1:C100))

To simulate an AND, instead of:

   =AVERAGE(IF(AND((A1:A100>1),(B1:B100="A")),C1:C100))

use:

   =AVERAGE(IF((A1:A100>1)*(B1:B100="A")>0,C1:C100))

Kevin
0
 

Author Comment

by:Hobart007
ID: 35443789
Thank you both for your answers.  The formula worked.  My issue now is that when I add the formula to a few hundred cells it crashes my pc every time I update something or try to filter my pivot table.

Is there a less system intensive way to do this?
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35444096
Array formulas are more CPU intensive than other formulas, but a few hundred should not affect the performance of your system. And they should most definitely not cause Excel or Windows to crash. Might be time to rebuild.

Kevin
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question