 ## July 11, 2006

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

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

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.

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?

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

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

Hi ele,

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

Solveig

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 .

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.

and like this too

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

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

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 ??

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

"500 < a < 1000"

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?

Thanks

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

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.

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!

The comments to this entry are closed.