In LibreOffice Calc, is it possible to use a complex function and a data range as arguments for a MIN() function?

I'm going to simplify a bit to hopefully get at the crux of my question.

I have a spreadsheet in which a cell has a massive formula. (Again, I'm simplifying to avoid a question that is so long no one will read it).

At the last minute, a new variable was introduced. If this new variable is less than the value of that formula, the formula should return this new variable instead.

This is a perfect use of the MIN() function, but I'm having challenges getting the MIN() function to use a complex formula (which has many IF statements in it) as one of its arguments and a data range as another.

For example, this did not work:

MIN(NEW_DATA_RANGE,IF(x=y/pi...))

Where NEW_DATA_RANGE is a data range containing the new variable.

If I replace NEW_DATA_RANGE with a literal constant, it works fine. Similarly, if I replace the formula with a literal constant, it works fine. But when I try to take the MIN() of a value in the data range and a complex formula, LibreOffice always returns zero (0).

Is this likely a bug in LibreOffice Calc, or is there some reason you can't use a data range and complex formula as arguments for the MIN() function?

1 Answer

Although the documentation isn't very specific about this, MIN() expects either a range of cells, or a list of values as parameters. As your function returns a value, the other parameters for MIN() also have to be values. So you need to pass NEW_DATA_RANGE as a parameter to another function returning a value. Eg.

=MIN(MIN(NEW_DATA_RANGE),IF(x=y/pi...))

If NEW_DATA_RANGE can contain an empty cell

=IF(ISEMPTY(A),IF(x=y/pi...),MIN(MIN(A),IF(x=y/pi...)))

I would replace the IF(...) by a user-defined function.