I just imported from OpenOffice Calc To LibreOffice and a Fuction is not working

English
#1

I just imported an openoffice spreadsheet that I have used for some time and has been working just fine. In the conversion to LibreOffice. A particular function is giving me a #VALUE! error. The formula is as follows:

=IF(ISBLANK(E67),"",SUMPRODUCT((‘Transfers and Deposits’.$B$89:$AA$94) * (‘Transfers and Deposits’.$B$89:$B$94=E67)))

Basically the formula is to sum the values in B 89 through AA 94 only if it finds E 67 in the column B 89 through B 94.

This runs perfectly fine in OpenOffice with the results I expect but I can not understand why its is not in LibreOffice. Everything else in this large spreadsheet works right but this function and it causes a cascading problem. If anyone can help I would appreciate it. I am making a big assumption that I can use this function this way in LibreOffice. Any help or direction would be appreciated. Thanks is advance !

#2

.odt ?

and more generally : This is the guide - How to use the Ask site? - #3 by Hrbrgr

#3

OpenOffice has the extension .ods

#4

Untitled.ods (12.3 KB)
OpenOffice returns 50.
LibreOffice #VALUE!

#5

I am running Both OpenOffice 4.1.15 and Libreoffice 24.8.4.2 on a MAC running MacOS Sequoia 15.2 with a M2 Pro processor .

#6

I appreciate you confirming the issue. If there is another way to achieve the same result I would appreciate the help. I do not have anything that comes even close to the simplicity of this formula and function that I can get to work. Thanks

#7

If your “you” is @Villeroy I would like to tell that column B contains strings, and SUMPRODUCT doesn’t ignore them as SUM() would do. (That’s not the complete problem. The bad products of texts with logical 1 or 0 should throw an error anyway.)
Therefore the #VALUE! error is to be expected in his sample sheet.
AOO Calc simply isn’t conformant with OASIS odf specifications insofar.

=SUMPRODUCT(('Transfers and Deposits'.$C$89:$AA$94) * ('Transfers and Deposits'.$B$89:$B$94=E67))

Would work for @Villeroy’s example in AOO and in LibO as well.

See attached example:
disask116358_AOO_missingPropagateError.ods (13.7 KB)

2 Likes
#9

Thank you for sharing your example with me. I modified the syntax of my formula to your example. Eliminating the compare column from the range to be totaled and the way it was written. It now looks like this:
=SUMPRODUCT($‘Transfers and Deposits’.D89:AA94 * ($‘Transfers and Deposits’.B89:B94=E67))

I will add the “IF” statement later. Unfortunately I am still getting the same result: #VALUE! … Very frustrating. the cells being compared B89:B94 and E67 are all defined as numbers even though they are characters. I also tried defining them as TEXT but it still did not work and gave me the same results… lost for words. Thank you for your time but I do not understand what I am missing here. Last point, Im new to LIBRE so I do not understand if I am suppose to just hit enter, which is what I did after entering the formula or CTRL SHIFT ENTER. I tried that too and no change in results. I’m trying to move all my OO Calc spreadsheet to LIBRE but if I can’t get this to work, I may not be able to. This is a key spreadsheet. My main reason is due to the fact that OO is unstable on macOS using Apple Silicon and obviously, LIBRE is so much more advanced in its development. Ugg if I can’t convert them.

#10

One last question before I give up. Is there a certain preference setting that I need to look at? I have already checked my settings between my OO Calc and LIBRE and they do look the same. Anyhow thanks for listening.

#11

Click View > Value Highlighting. Numbers will be in blue, text will be in black. Look in Transfers and Deposits’.D89:AA94 for black. Numbers formatted as text probably won’t cause a problem for the formula but if there are any actual letters in the range then you will get an error, same for numbers with spaces between like 2 2
.
SUMPRODUCT doesn’t need Ctrl+Shift+Enter

#12

@Sphex1411:
As long as you don’t attach an .ods showing the problem for you, any attempt to find the cause means stabbing in the dark and wasting time.

Just one general hint:
Getting errors with formulas containing subexpressions (probably nested on more than one level), Extract the subexpressions (top down) as TEXT strings, and paste them into cells in an otherwise unused range of the sheet.
Then you can, starting on top level, prefix an “=”, and press ENTER or Ctrl+Shift+ENTER again.
As soon as you get a related error this way, you will know where to continue the research.
(If your formula depends on direct references to rows or columns of the current position, ROW() e.g. you need additional measures.)
But no contributor can write an extra tutorial about “How to research error causes” every time a user comes with a problem and gives too little information. More specific answers mostly depend on knowing a specific case to sufficient detail.

#13

I found the issue and thank you for your time. it was right in front of my eyes. The “IF” statement itself was the problem because it would put “” in that range individually If the compare was true. So when a false condition occured the range had some nulls. I changed the If(ISBLANK(e67),"" to if(ISBLANK(e67),0 as well as not including the columns B and C in the range and started with D89. I appreciated the nudge in the right direction. Thanks again.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.