Basic conditional functions in Excel

[Macbeth]

Sponsored Links

I began to learn Excel in detail and according to me its really hard to "make" Excel to use basic functions like conditions. I too led the mode of programming languages such as C, Java or PHP. Could someone explain how to do a test, such as this:

 = if ( ( B1<> "" ) and ( C1= "M" or ( C1= "S" ) or ( C1= "C" ) ) )

I thought to do it this way:

=IF ( AND ( B1<> "" ;OR ( C1= "C" ;C1= "M" ;C1= "S" ) ) ; "modify" ; "" )

And in my case it works, but I carry too much trial and error instead of it logically. Can I have more explanations?

[kelfro]

There are certainly many methods, I opted for the audit function "or" combined with the first checks only on B1. It therefore gives two 'if' knowing that in Excel syntax is:
If (argument: if True: if False)

In summary, if cell B1 is not empty then it checks cell C1.

[Macbeth]

Thank you this solution works for me.

If possible, I want it really with an explanation, because I still find it difficult to reason with the IF (OR...). It is like writing reverse polish I flew in a td. It seems much less logical than writing if (condition or status). I would like to change that!

[fellah]

I understand that there are 2 things that disturb you:

1. syntax that seems chaotic on (particular) AND and OR. Conceptually, this is not the same as what you have to be used:

- I imagine that you would manipulate OPERATORS, which enables you to link elements placed on the left and right of the 'AND'
=> If ((A1 <>"") AND (C1 = "M ").......

- In Excel, they are not operators but FUNCTIONS! And as we call a function by naming and then passing it parameters separated by semicolons, that gives
= IF (AND (condition1 ; condition2); ResultIfTrue; ResultIfFalse)

In fact, for appropriate function AND you can handle it alone, for example like this:

  =AND(A1 = "rain", B1 = "sun").

It returns TRUE or FALSE

While an operator is more intuitive, because it is closer to the structure of a sentence in spoken language. But you get used to it, you'll see.

2. readability formulas

In Excel, when you go out for simple formulas you quickly find yourself with dozens of characters behind a "=" in a cell, with parentheses to separate your levels of nesting, and everything is spread over several lines ...

This is significantly less readable (understatement) that the structured code with nice indentation and all, but Excel does not leave you much choice.

Ultimately, you can put line breaks within the formulas - it is by Alt + Enter and it gives this:
= IF (AND (condition1; condition2);
ResultIfTrue;
ResultIfFalse)

Hoping that it helps you ...

[!const]

For
bla AND bli AND blu
we do in excel as

= AND (bla;bli;blu)

and we have the result as
TRUE If bla = TRUE AND bli = TRUE AND blu = TRUE (all conditions are true)
FALSE if bla = FALSE OR bli = FALSE OR blu = FALSE (at least one condition is false)

For
bla OR bli OR blu
we do in excel as

=OR ( bla;bli;blu )

and we have the result as
TRUE If bla = TRUE OR bli = TRUE OR blu = TRUE (at least one condition is true)
FALSE if bla = FALSE AND bli = FALSE AND blu = FALSE (all conditions are false)

In both cases, you can put no. of parameter you want and you can nest them.

AND and OR returns resulting output necessarily a boolean (TRUE / FALSE)