Ask Your Question

# How to make LibreOffice Calc treat empty cells as zeros when using ranges in formulas?

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.

edit retag close merge delete

## 2 Answers

Sort by » oldest newest most voted 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.

more

## Comments

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

## Comments

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.

## Stats

Asked: 2017-04-06 17:16:10 +0200

Seen: 1,214 times

Last updated: Apr 07 '17