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

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?

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

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

Yes, this one is great! Worked directly. Even though @ROSt53 '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? :slight_smile:

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].

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

=IF([fct]<0, 0, IF([fct]>100, 100, [fct])

Could you correct that?