Appropriate Logical Function to Use forMultiple Grouping of True Criteria

[Dursa]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I currently have a permutation of 24 possible outcomes to conditions of 3 sets of groupings. Set 1 has 2 elements with only 2 possible combinations of conditions, which are linked to the 2nd and 3rd sets. The 2nd set has 2 elements, each having 3 three possible states, deriving 6 possible combinations of outcomes; whilst the 3rd set has 2 possible states for 2 elements, deriving a possible 4 combination of conditions.

In totality, there are 24 possible combination of states, each having a different formula. How do I, in Excel, assign my own formulas for each of the 24 possible combination of states; for example. IF condition 1 is true THEN formula 1, ELSE IF condition 2 is true then formula 2, ELSE IF condition 3 is true THEN formula 3.......... up until formula 24? In other words which is the appropriate Function or Functions to use and How should it be set out in a cell on an Excel Spreadsheet?

Anwar

 

[katie_c]

There is a limit of 7 when it comes to nesting if statements in Excel. So it depends on the types of evaluations you are doing for this to work - it most likely has to be done in multiple cells.

If possible, break the evaluation up into multiple formulas... and combine formulas that way. If you have a specific example, that would be helpful in figuring out the best combinations of formulas to use.

-Katie

 

[CyberTaz]

In addition to Katie's reply you might investigate the nesting of AND(),
OR(), & NOT() functions within your IF()s.

 

[Dursa]

There is a limit of 7 when it comes to nesting if statements in Excel. So it depends on the types of evaluations you are doing for this to work - it most likely has to be done in multiple cells.

If possible, break the evaluation up into multiple formulas... and combine formulas that way. If you have a specific example, that would be helpful in figuring out the best combinations of formulas to use.

-Katie

P Q A B C W X Y Z
1 + + = ≻ ≺ ≺ ≻ + + − + − − + −
2 - + = ≺ ≻ ≻ ≺ + + − + − − + −

I hope that the format of the above table is compatible for the website.

Two variables 1 & 2 have a possible combinations of states P or Q AND three possible combinations A (although 2P will be -2 (value) to equate the positive of 1, or B or C AND four possible combination of states W or X or Y or Z.

In total there are 24 combined states (as the criteria) for 1 and 2; each in turn having a different THEN formula command, which I have already written out.

The above is fairly detailed and complex, but I hope that it would be understood.

Dursa

 

[Dursa]

There is a limit of 7 when it comes to nesting if statements in Excel. So it depends on the types of evaluations you are doing for this to work - it most likely has to be done in multiple cells.

P Q A B C W X Y Z
1 + + = ≻ ≺ ≺ ≻ + + − + − − + −
2 - + = ≺ ≻ ≻ ≺ + + − + − − + −

I hope that the format of the above table is compatible for the website.

Two variables 1 & 2 have a possible combinations of states P or Q AND three possible combinations A (although 2P will be -2 (value) to equate the positive of 1, or B or C AND four possible combination of states W or X or Y or Z.

In total there are 24 combined states (as the criteria) for 1 and 2; each in turn having a different THEN formula command, which I have already written out.

The above is fairly detailed and complex, but I hope that it would be understood.

Dursa

Apologies, the table format is not compatible for the web-site as the header rows P through to Q as misaligned. I will attempt to reply again later.

Dursa.

 

[Dursa]

I have attempted the approach and you are both correct. However, the formula builder in Excel for Mac 2008 is not at all forgiving if you miss a step or go to the next step in an incorrect sequence; it does not accommodate going back to the previous condition. One has to start all over again with entering the functions, and with so many criteria the likelihood of not missing a step or the not needing to correct a typing error is very small indeed .

 

[CyberTaz]

Unfortunately, you are quite correct. I'm not even sure it was designed for
the construction of more complex equations. You might have more success
working in the Formula Bar or directly in the cell.

Another suggestion that has worked well for me in the past... Don't type the
= to begin with. Instead, just enter the formula as a text string. Once
you're confident that all the necessary arguments have been supplied, the
parens are in the right places & the logic looks logical add the = at the
beginning & let it rip. If you've worked in the formula bar or directly in
the cell you will not lose anything of what you've written.

Either location -- but especially the Formula Bar -- will also be far more
readable & intelligible than the Formula Builder limitations provide.

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[Dursa]

Well done to all.

Whilst, Excel for Mac is far from perfect; it was human intellect and experience of those that responded that more than compensated for the limitations of Excel for Mac; more specifically the formula builder.

Thank you.
Dursa