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")
formula
smiranda wrote on December 31, 1969 19:00 EST
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 wrote on December 31, 1969 19:00 EST
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 wrote on December 31, 1969 19:00 EST
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 wrote on December 31, 1969 19:00 EST
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 wrote on December 31, 1969 19:00 EST
Hello,
Could anybody tell me that why +1 sign is added in the choose function...what does it mean?
zouhair dawood wrote on April 18, 2006 06:43 EST
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 wrote on December 31, 1969 19:00 EST
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 wrote on November 30, 2006 23:53 EST
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 wrote on December 31, 1969 19:00 EST
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 wrote on December 31, 1969 19:00 EST
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?
Terms
and Conditions of use
The applications/code on this site are distributed as is and without warranties
or liability. In no event shall the owner of the copyrights, or the authors
of the applications/code be liable for any loss of profit, any problems
or any damage resulting from the use or evaluation of the applications/code.