» Simplifying Formulas by Reducing the Complexity of IF Functions
CATEGORY - Logical Formulas
VERSION - All Microsoft Excel Versions
The range A2:C8 contains rows of numbers. For each row, we want an indication of how many of the following conditions are met:
1. Num1 (column A) = 2
2. Num2 (column B) = 4
3. Num3 (column C) = 6
The following formula based on nested IF functions, is very complex:
=IF(A2=2,IF(B2=4,IF(C2=6,"All Conditions","Two Conditions"),IF(C2=6,"Two Conditions","One Condition")),
IF(B2=4,IF(C2=6, "Two Conditions","One Condition"),IF(C2=6,"One Condition","None of the Conditions")))
Is there a simpler way to achieve the same results?
Solution:
Use the CHOOSE function as shown in the following formula:
=CHOOSE((A2=2)+(B2=4)+(C2=6)+1,"None of the Conditions","One Condition","Two Conditions","All Conditions")
Book Store:
Recommended Books:
- Microsoft Windows XP Inside Out
- What the IRS Doesn't Want You to Know: A Cpa Reveals the Tricks of the Trade
- The McGraw-Hill 36-Hour Course in Finance for Nonfinancial Managers
- Yes, You Can Time the Market!
- Real Estate Loopholes: Secrets of Successful Real Estate Investing
- F1 Get the Most out of Excel! The Ultimate Excel tip Help Guide
formula
smiranda
new to excel need formula for colomn M. need column H to subtract total from columns i, j, k, l giving me total on column M.
on Conditional flag subtract and add from other sheet
SAM
i want on basis of conditional flag paid or not i want to add or subtract amount from other work sheet and display in the current sheet need help
Linking Text
David
I need a formular that will be able to link text from one work sheet to the other or numerouse work sheets ,such as name.
Adding IF statements
Andrew
I have 2 "IF" statements with 7 and 5 conditions, respectively. When these statements are in the cell singly, they give me the numerical result I'm expecting, which can be summed. However, when I add the IF statements together using "&" it results in the right answer, but in a text form which I cannot sum. Thanks in advance for any help on this matter.
could you tell me why do you add +1
eddie
Hello,
Could anybody tell me that why +1 sign is added in the choose function...what does it mean?
zouhair dawood
Hello, could anybody tell me how to get the result automatically of the difference between Debit Colum and Credit Colum, in a way that if the difference between debit and credit greater than the result goes to another debit colum, and if the difference between debit and credit is smaller than the result goes to another credit colum??
help me
Mukul Soni
hello,
can anybody tell me how to use if formula in a marksheet while getting all conditions right
Help on VBA macro to delete certain sheets from workbook
xamil
Does anyone has a Excel VBA macro that deletes a series of sheets with similar names, such as sheet1, sheet2, ...sheetn from the workbook if those sheets exist. I mean if those sheets exist then delete them.
The logic is like this: If sheetname ="sheet1" or "sheet2" or ... "sheetn" then delete;
many thanks in advance !!
If Functions
Missi
IF FUNCTIONS SUCK! SORRY BUT THIS STUFF IS CONFUSING, I HATE IT WITH A PASSION, JUST THOUGHT YOU MIGHT WOULD LIKE ME TO SHARE EXACTLY HOW I FEEL ABOUT THE SITUATION
MAY THE LORD POUR OUT BETTER BLESSINGS THAN THIS MESS, AMEN
How do you.........
Fern A Olson
I think Excel should be re-created to a more simplier language for people to totally understand. To many side tricks to get the results you need. I too want to know what does the 1+ means in a IF formula?
