I need to take the minimum over a range of cells, treating empty cells as zeros. However, both MIN and MINA functions ignore empty cells (in LibO 5.1). What can I do?
More precisely, what I currently need is to calculate the minimum of each line in a table treating empty cells as 0 and to store the results in the last column.
You can use the N function to convert non number content to zero. Do not finish entering be clicking the check mark, but finish by Ctrl+Shift+Enter to get a so called array formula or matrix formula.
For the average of the range A1:B4 use =AVERAGE(N(A1:B4)), for example.
To get an array formula, you can use the function wizard as well and check this option left bottom in the dialog. For details on array formulas have a look into the in-built help.
Thanks, it seems to work in an array formula but strangely it does not in a usual one. I need in fact to calculate one column as the minimum of the preceding columns, line by line. I have not figured out yet how to do it with an array formula.
I have added some remarks on array formulas. If it still does not work for you, post a new question.
I have looked into the documentation, but so far did not figure out how to calculate minimums line by line with array formulas without creating manually a new array formula in each line. My question initially was about ordinary formulas. Is this not possible with ordinary formulas? Is this a bug, or is there a reason why N is more appropriate in array formulas than in ordinary ones?
Summing zero to the cells in the formula, and using SUMPRODUCT() to forces the array without introduce the formula as it, I think works as you expect.
If you make a new column and calculate min or max or average depending on the new column, then a simple =A1 and =A2 and so on in column B will do.