Ask Your Question

Is it possible to use more than one test in the IF() function in Calc? [closed]

asked 2013-02-27 07:35:30 +0100

chtfn gravatar image

updated 2015-10-26 22:11:03 +0100

Alex Kemp gravatar image

Hi there

I was wondering if it is possible to use more than one test in the function IF().

What I want to do is changing the value to a fixed value if it drops below 0 or goes above 100.

At the moment, I am using

=IF([function]<0, [function], 0)

however, I would like to add this element too:

=IF([function]>100, [function], 100)

Is it possible to do both in one function?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-24 15:45:23.511822

2 Answers

Sort by » oldest newest most voted

answered 2013-02-27 08:08:04 +0100

JohnSUN gravatar image

You can do this even without the function IF (). For example, such a formula

=MAX(0; MIN(100; [function]))

will perform the same test and give the desired result

edit flag offensive delete link more


@UbuntuBrisbane -- Did @JohnSUN's answer work for you? If so, please mark it as correct so we can resolve this question. Thanks!

qubit gravatar imagequbit ( 2013-03-08 04:17:42 +0100 )edit

Yes, this one is great! Worked directly. Even though @ROSt52 's answer helped putting me on the right track and was using IF=, yours definitely makes it simpler.

Only funny thing is I think MAX and MIN should invert names, unless they mean "maximize" and "minimize? :)

chtfn gravatar imagechtfn ( 2013-03-10 00:21:03 +0100 )edit

answered 2013-02-27 09:17:35 +0100

ROSt52 gravatar image

An alternative to the interesting min-max function @JohnSUN explained you also can use

=if([fct]<0,fct=0,if([fct]>100,100, xxx))

For xxx you must find another thing the if-combination should do; e.g. replace xxx by [fct].

edit flag offensive delete link more


Thank you for your answer. Actually, your function should be more like:

=IF([fct]&lt;0, 0, IF([fct]&gt;100, 100, [fct])

Could you correct that?

chtfn gravatar imagechtfn ( 2013-03-10 00:14:48 +0100 )edit

Question Tools


Asked: 2013-02-27 07:35:30 +0100

Seen: 175 times

Last updated: Feb 27 '13