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.
cmoerbeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Thymos68Director of OperationsCommented:
IMO you should substitute the hardcoded requirements with references.

=IF(B2<C2,"Not Enough Fruit","Enough Fruit")
2
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")
0
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.

Thanks!
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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.
0
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.
0
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.
0
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

Thanks
0
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cmoerbeAuthor Commented:
Amazing. The lookup worked perfectly.

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

Cheers!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.