Ask Your Question

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

asked 2017-04-06 17:16:10 +0100

alexeymuranov gravatar image

updated 2017-04-06 18:24:15 +0100

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 flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2017-04-06 17:48:31 +0100

Regina gravatar image

updated 2017-04-06 20:27:18 +0100

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.

edit flag offensive delete link more


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.

alexeymuranov gravatar imagealexeymuranov ( 2017-04-06 18:11:01 +0100 )edit

I have added some remarks on array formulas. If it still does not work for you, post a new question.

Regina gravatar imageRegina ( 2017-04-06 20:28:40 +0100 )edit

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?

alexeymuranov gravatar imagealexeymuranov ( 2017-04-06 21:47:19 +0100 )edit

answered 2017-04-07 01:24:02 +0100

m.a.riosv gravatar image

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.

image description

edit flag offensive delete link more


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.

Regina gravatar imageRegina ( 2017-04-07 20:33:00 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


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

Seen: 982 times

Last updated: Apr 07 '17