Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



Basic conditional functions in Excel

Software Development


Reply
 
Thread Tools Search this Thread
  #1  
Old 01-12-2009
Member
 
Join Date: Nov 2009
Posts: 47
Basic conditional functions in Excel
  

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:
Code:
 = if ( ( B1<> "" ) and ( C1= "M" or ( C1= "S" ) or ( C1= "C" ) ) )
I thought to do it this way:

Code:
=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?

Reply With Quote
  #2  
Old 01-12-2009
Member
 
Join Date: Apr 2008
Posts: 2,000
Re: Basic conditional functions in Excel

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.
Reply With Quote
  #3  
Old 01-12-2009
Member
 
Join Date: Nov 2009
Posts: 47
Re: Basic conditional functions in Excel

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!
Reply With Quote
  #4  
Old 01-12-2009
Member
 
Join Date: May 2008
Posts: 685
Re: Basic conditional functions in Excel

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:
Code:
  =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 ...
Reply With Quote
  #5  
Old 01-12-2009
Member
 
Join Date: Nov 2008
Posts: 1,054
Re: Basic conditional functions in Excel

For
bla AND bli AND blu
we do in excel as
Code:
= 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
Code:
=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)
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Basic conditional functions in Excel"
Thread Thread Starter Forum Replies Last Post
What are the basic functions of hard drive? Bruno007 Hardware Peripherals 2 15-02-2012 05:12 PM
Excel Macros for conditional formatting statement in Excel Kungfu Pandey Microsoft Project 1 08-01-2012 07:35 PM
lack of some basic functions in Nokia c3-02 Mudhol Portable Devices 4 29-09-2011 03:51 AM
Basic information to understanding the formulas and functions of Excel Daniel23 Windows Software 4 27-11-2009 07:55 AM
Conditional formatting in excel RockeЯ Windows Software 4 27-06-2009 12:06 PM


All times are GMT +5.5. The time now is 10:34 AM.