The Immense Potential of the IF Function in OpenOffice Calc

The word IF has so much potential.

If I win the lottery...if I lose 20 pounds by next Friday...if only I had gone to France that summer...if blind monkeys from hell fly out of my butt, I will certainly attend that concert with you...

IF is a powerful word.

It's also a powerful function in OpenOffice.org. You can use IF to control the content of a cell depending on the content of another cell, on the result of a calculation—and that means the possibilities are limitless.

Syntax

First, what's the syntax?

 

So you could have this, if you needed to have different values depending on whether the value in cell B5 is bigger than the value in cell B6. Note that the test and both results can all be plain values, can be cell references, or can be calculations.

 

Or you could do this, in case you just want to print something in a cell depending on whether the contents of cell B5 are over a particular value.

How to Use IF

Click in the cell where you want the variable results to show up.

 

Then you can just type the formula.

IF (B5 > B6;G10;G10/G2)

Or you can click the Formula icon.

 

Type I in the list to go down to the IF function, double-click IF, and type the values you want in the window. You'll see the result in the window. When you're done, click OK.

Example

Here's an example. I've got this spreadsheet. I want to always have the right recommendation about what to do in the Recommendation field, and the calculations can change anytime. Also, the calculations might bring in cells from other sheets as well, so it might not always be as simple.

1. Click in the cell where the results should show up.

 

2. Click the Function icon.

 

3. Find IF (they're in alphabetical order).

 

4. Double-click IF.

 

5. Type the test (whether the consulting revenue is bigger than writing revenue)

 

6. Type what I want to have in this cell if that's true, with quotes since it's text

 

7. Type what I want to have in this cell if that's false, with quotes since it's text. Click this image to see it bigger.

 

8. Click OK.

 

And here are the results.

 

When I double-click the cell I can see the formula bigger. (Click this image to see it full size.)

 

I've shown some simple examples, but when you think about it, IF can be used effectively in far more powerful situations....IF you think about how to apply it the next time you bring up a spreadsheet.

 

---

Comments

[Paul Chillingworth]

Good explanation. Can the IF function be used to give a resulting value of statement is another cell contains a certain word?

[Solveig Haugland]

Hi Paul,

I would think so....you'd need to find the function for "includes" because just using the equal sign would require more restriction. I'm not sure off hand but if you search in the help or the function wizard (the icon next to the sum icon on the toolbar) it should be there. Perhaps SUBSTRING or something. If you go to openofficetips.com which is focused on spreadsheets you should get some info there too.

Solveig

[Paul Chillingworth]

I have now found an answer. I am running a spreadsheet todo list for a complex office fitout. I have a column that records if a todo is a design change from the original contract or a change requested by the client. In the adjacent column I need to record if there is ANY type of change, so the data pilot can show all changes or issued changes. (apologies if this is too much information). I have used the formula

=IF($J175>"Design";"design change";"no design change").

It works.

I wish there was a shortcut for inserting the current date.

[morphingstar]

IF cell A="A" then display "AA" else IF cell B > 0 then display "big" else "below 0"

Cell a1 displays A
Cell b1 displays +1

=IF(a1="A";"AA";(IF b1>0:"big";"below zero"))

Why does the nested IF not work?

[Solveig]

Hi morphingstar ,

You need to have extra parentheses around the nested IF, and you've got a colon in there instead of a semicolon. This works.

=IF(A1="A";"AA";(IF( B1>0;"big";"below zero")))

Solveig

[ele]

do you know if there is a limit to the nested IFs I can put in a single cell?

[Solveig]

Hi ele,

I'm not sure what the limit is, if there is one. Love'em, though.

Solveig

[Tibi, RO]

hello all.
my question si this:
i need to see all cells in collumnb that have the numbers between 500-1000) and it does not work.

if ( 500< B1 <= 1000, 1; 0;) so if i input cell B1 6400 result is 1

what hapeens if i neet to extend my search like this:
if b1 is between 500-1000 display 1, else 0
if b1 is between 1000-2500 display 1, else 0
and so on .

[tibi]

i have tried also nested if

=IF(K3*L3>500;1;IF(M3<=1000;1;0))

where

K3 L3 M3
80 80 6400
result is still 1.

[tibi]

and like this too

=IF(K3*L3>500;1;(IF(M3<=1000;1;0)))

[tibi]

in the mean time i have found a partial solution to my preoblem:

A1000;"too big";(IF( A2>500;"in";"too small")))

10002500;"too big";(IF( A3>1000;"in";"too small")))

25005000;"too big";(IF( A4>2500;"in";"too small")))

500010000;"too big";(IF( A5>5000;"in";"too small")))

----------------------

it works also reversed :

500<=A<1000
=IF(A14<500;"too small";(IF( A14<1000;"in";"too big")))

but i din not solve this problem.
how i do this :

2500<=a<=5000

[tibi]

previoius post din not display well
i go again:

5001000;"too big";(IF( A2>500;"in";"too small")))

reveresed:

500<=A<1000
=IF(A14<500;"too small";(IF( A14<1000;"in";"too big")))

an the final question :
how i do this :

2500<=a<=5000 ??

[Tibi]

i got another one
=IF(A17>500;IF(A17<1000;"in";"mare");"mic")

"500 < a < 1000"

[idave147]

hello Solveig,

A single IF ELSE statement is: IF(A1="M";5;10)

How do I make it work for several values? i.e.

IF(A1="M" OR "N" OR "P";5;10)

[for values M,N,P = 5 or else =10]

What is the syntax?

I'd appreciate your help
Thanks

[Carla Munoz]

Paano ba to???? PLEASE HEEEEELLLLP!!!

[David]

Hi Solveig Guru

Can I use the IF() function in the production of labels if so where is it inserted?

E.G. If Column A = "X"(nextrecord,print)?

Your help is invaluable and should replace the Help in OpenOffice, which although passable is very limited especially with labels!

Many thanks

David

[Adam]

Hi,

I have been struggling and this forum is the closest to an answer.

I have two columns. Each cell in one column has multiple words.

Eg one two three.

The column next to it has a number.

I need to count that number only if it has the text two in the adjacent cell. I cannot work out how to do it. Any help would be appreciated.

[Mitch in VA]

Solveig- That you SOO MUCH for this Blog!

My question: I am working on a (not so) simple spreadsheet for rental income. I have one column for monies paid "in" and one for monies paid "out". Obviously, if there is an entry for money "in", there cannot be one also for money "out" on the same line. How do I prevent an entry in both columns - it an either/or choice. Thanks a ton!