• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 72
  • Last Modified:

Using multiple IF AND statements in Excel formula

I am trying to compare multiple different cells from multiple columns in an Excel spreadsheet.

Here is my example data:

Type          Amount      Requirement        Status
Apples          10                  20               Not Enough Fruit
Oranges          20                  30               Enough Fruit
Banannas  5                     30               Enough Fruit
Peaches          5                          60               Enough Fruit

As you can see it works right at first, but as it goes on it starts to produce incorrect "Enough Fruit" results.

Here is the formula I use to get the data in the "Status" Column.

=IF(AND(A2="Apples",B2<20),"Not Enough Fruit", "Enough Fruit")

As you can tell this formula uses only the correct threshold for Apples, but I need this formula to contain all "Requirements" for each fruit type within the formula, so that when i drag the formula it works for all types.

I have tried many ways to append additional (IF (AND) statements to the end of the query, further nesting, etc. etc. but cannot seem to get it to work right at scale.

Any ideas on how I might be able to solve this would be immensely appreciated.
2 Solutions
Thymos68Director of OperationsCommented:
IMO you should substitute the hardcoded requirements with references.

=IF(B2<C2,"Not Enough Fruit","Enough Fruit")
Christopher RourkeProduct Manager @ Experts ExchangeCommented:
I agree w/ Thymos68 but he forgot to account for the equal to case. I'd also recommend inverting the true/false (if for no better reason than to accentuate the positive):

=IF(B2>=C2,"Enough Fruit","Not Enough Fruit")
cmoerbeAuthor Commented:
Each value in A will have a different threshold. That's why I was on the path of trying to add a separate one for each line item. I can try your suggestion though and see if it works. Please stand by.

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

cmoerbeAuthor Commented:
I am not sure this will work. Apples has to be 20, Oranges has to be 30. There will be repeating apples / oranges / etc in the list. So each one has to be identified by type and then compared to threshold.

I need something that functions like this and accounts for each type.

=IF(AND(A2="Apples",B2<20),"Not Enough Fruit", "Enough Fruit"), =IF(AND(A3="Oranges",B3<20),"Not Enough Fruit", "Enough Fruit"), etc, etc.
cmoerbeAuthor Commented:
In other words it has to be apples AND 20 or more on hand. If less than 20 apples on hand then "Not enough fruit"

Then if i go to oranges, etc. I need the formula to already have the threshold stored for that type. Bananas and so on.

I hope that helps.
Thymos68Director of OperationsCommented:
Although you can build your thresholds into a nested formula, it would be needlessly complicated.  Fine if you only have 8 items, or 12 or 20... but what if you had 100 items??  There is a much easier way to get what you're looking for by comparing one cell to another or doing a vlookup to a list of fruit to get each fruit's individual threshold number.
cmoerbeAuthor Commented:
It is only 4 items that I am needing to work with, but I do see your point of it not being easily expandable.  I did briefly look at using VLOOKUP for this, but being more familiar with loops than VLOOKUP i figured I would go with what seemed more familiar.

Ill try to correlate my problem to your suggested solution if you think that is a functional path forward.

Please stand by

Wayne Taylor (webtubbs)Commented:
I agree with Thymos68 - nested IFs will be messy.

You could store the thresholds in the workbook somewhere and reference that with VLOOKUP, or you could insert them into the formula using either VLOOKUP or CHOOSE...

=IF(B2<VLOOKUP(A2, {"Apples",20;"Oranges",30;"Banannas",30;"Peaches",60},2,0), "Not enough fruit", "Enough Fruit")

=IF(B2<CHOOSE(MATCH(A2, {"Apples","Oranges","Banannas","Peaches"},0), 20,30,30,60), "Not Enough Fruit", "Enough Fruit")

Open in new window

Both formulas above do the same thing and up to you which is easier to manage.
cmoerbeAuthor Commented:
Amazing. The lookup worked perfectly.

Thank you both for the tips. I'll will definitely use VLOOKUP more often.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now