IF/THEN/ELSE Statements in Calc

[svavrek]

Okay, I have something that, in VB or C++ would be easy... somehow, I cannot get it in Calc...

I have two cells. A1 and B1, lets say. Based on the input of A1, I want the value of B1 to change... Here's an example.

A1 = Input
B1 = Value

IF A1="3.75" THEN B1 = "1/4"
ELSE
IF A1 = "6.00" THEN B1 = "3/8"

And so on... making a sheet that calculates thickness of material based on material cost, or I can even do it the other way where the material thickness is entered and the cost is calculated. Would like a drop down or something even to make it easier...

[FJCC]

The form of the IF() function is

Code: Select all

=IF(Condition;ThenDoThis;ElseThis)

so in you case B1 would contain

Code: Select all

=IF(A1="3.75";"1/4";IF(A1="6.00","3/8";"No Match"))

However, I wouldn't use nested IF() functions to do this. After a few levels the function gets unwieldy. Try a VLOOKUP(). In some convenient place, say E1:F2 make a table

Code: Select all

3.75   1/4
6.00   3/8

In B1 you enter

Code: Select all

=VLOOKUP(A1;E1:F2;2;0)

The function will look for an exact match of A1 in column E and return the matching value from column F.
Also, you have written your question as if 3.75, 1/4 etc. are text. If you are actually trying to match numeric values, don't put them in quotes.

[oo_usr]

I also have a problem with an IF formula.

What I want is the following:
=IF((D40="");"";(IF(B40=D40)~5+B40*2~ABS((B40-D40)*(-2)))) -> Returns in "Err:502"

So what I want is if D40 is empty, write nothing, otherwise IF B40 and D40 are the same make 5+2*B40 and if this is also not true (not empty and also not the same) do ABS((B40-D40)*(-2)).

So there are three different version possible: 1. Fields are empty, 2. fields not empty but are the same and 3. fields are not empty and not the same.

It works when I combine two of these but it does not work with all three.

Someone knows why? What am I doing wrong?

[Zizi64]

This formula works for me fine:

Code: Select all

=IF(D40="";"";IF(B40=D40;5+B40*2;ABS((B40-D40)*(-2))))

[oo_usr]

Yeah I tried again, works now for me too! Don't know what I made different. Thanks for your fast reply!

[Lupp]

Yeah I tried again, works now for me too! Don't know what I made different. Thanks for your fast reply!

You originally entered

Code: Select all

=IF((D40="");"";(IF(B40=D40);5+B40*2;ABS((B40-D40)*(-2))))

The formula parser had to find the subexpression

Code: Select all

IF(B40=D40)

therefore, which is a complete IF-expression syntactically due to the closing parenthese. The subsequent ";" (2 occurrences) are unrecognised delimiters now and are both replaced by "~" which is the character hinting this problem. The expression as a whole is nonetheless accepted and will only return an error if the first condition comes out FALSE and an attempt has to be made to evaluate the ELSE-part. The error "Err:502" will temporarily disappear as long as D40 is blank (or containing the empty text).

(The specifications allow for shortened IF expressions where the third or both the third and the second parameters are missing. If a missing parameter has to evaluate it will return the logical value of the condition. I personally do not judge this reasonable.)