Ask Your Question
0

How can I multiply value with empty cell in Calc (now gives #Value! error) [closed]

asked 2013-07-31 13:00:02 +0200

Georgi gravatar image

Hello everybody, I've received a file made with OpenOffice Calc and when I open it in LibreOffice it gives strange error. There is a formula from that type - "=$B5*IF(ISNA(H5);0;H5)" that multiply the empty cell "H5" with value in "B5" and it gives me #Value! error. What could be wrong? When open on the computer with OpenOffice the result is 0. I've made a small test with starting new file in Calc and then I multiplied value with empty cell and it gives correctly 0. But in this file it could not handle empty cell formula. any help will be welcome, thank you

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-11-05 06:01:19.065201

Comments

Just replace formula with "=IF(ISERR($B5*H5);0;$B5*H5)" IMHO, cell H5 is not empty, there is blank (space)

JohnSUN gravatar imageJohnSUN ( 2013-07-31 13:34:09 +0200 )edit

2 Answers

Sort by » oldest newest most voted
2

answered 2013-07-31 13:50:13 +0200

oweng gravatar image

Tools > Options... > LibreOffice Calc > Formula > Detailed calculation settings section > select the Custom option and click the Details... button > set Treat empty string as zero to "True".

edit flag offensive delete link more
0

answered 2013-07-31 16:29:43 +0200

Georgi gravatar image

thank you! It is really a hidden option, I've tried to search it in the help, but with no success. Now it is working as expected.

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2013-07-31 13:00:02 +0200

Seen: 2,383 times

Last updated: Jul 31 '13