Calc - IF, AND, OR

IF, AND, OR

Combining IF, AND & OR can be rather helpful when there is more than one question to solve.

Say, a cell is to calculate the net price of an item only if the model is in stock and the colour of this item is green, otherwise the cell is to show "--".

Or, if comparing an actual sales number to the budget number, display nothing when one of the two numbers is zero, otherwise calculate the percentage actual versus budget.

Or, if comparing the actual number of music CD's on your shelf is bigger than the ones that should be there, display "hhmm", or if the number of CD's on your shelf is smaller than it should be, display "hhmm", otherwise display "OK", because then everything matches. I know a person who counts CD's, so I guess there must be more of those people around.

=IF(AND(B5>0;C5="green");D5/B5;"--")	If the item (B5) is in stock and the item is green (C5), then calculate the net price per piece (D5 is the net price of all items in stock), otherwise display "--"
=IF(OR(E30=0;F30=0);" ";E30/F30)	If the actual sales no. in cell E30 is zero or the budget number in F30 is zero, display nothing, otherwise calculate the percentage.
=IF(OR(K200>L200;L200>K200);"hhmm";"OK")	If the number of CD's on the shelf doesn't match the number that should be there, display "hhmm", otherwise display "OK".

These pages are available as print-friendly PDF (click icon).