Bug 135551 - =min() intolerance to text in fields
Summary: =min() intolerance to text in fields
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: All All
: medium normal
Assignee: Not Assigned
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
Reported: 2020-08-08 10:28 UTC by Kęstutis Snieška
Modified: 2020-08-10 22:50 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:

File from description (65.00 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-08-09 19:00 UTC, Roman Kuznetsov
Modified attachment to showcase the situation (67.60 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-08-10 19:30 UTC, Kęstutis Snieška

Note You need to log in before you can comment on or make changes to this bug.
Description Kęstutis Snieška 2020-08-08 10:28:13 UTC
    The cell in the O column is =min(HRowNumber:NRowNumber). The result for DIN912 M10x240 and M10x260 are "out of space": arbitrary values, not any of the values it is applied to.

The document is made with LibreCalc on Windows 7 with Russian Locale (the formula is entered in russian, originaly, but even if I open it on a machine with english locale and correct it, it still misbehaves).
Interestingly, If import it to google sheets, it does misbihave there too. (I have already reported it to google).   
    Here is the link to the .ods file:

   A bit older version of the spreadheet (https://drive.google.com/file/d/1mzZmbnXj25-L2CDeP7au1UTlXUHBEA8O/view?usp=sharing , 
   does not exibit the error if I open it on another machine with English/Lithuanian locale, do a colum inster and appy the formula(=min()). Tested it with LibreOffice (x64), Portable Apache OpenOffice 4.7 and Google Calc).
   Something is wrong with my LibreOffice, which does not like ascii strings in cells using =min() : if the text is present, it shows error "#ЗНАЧ!" (#VALUE!). If I delete the text, then it shows another error: "#ДЕЛ/0!
" (#DIV/0). Interestingly, that same document, when imported to google docs, does behave the same: same mistakes, same strange values with the mentioned strings.

Steps to Reproduce:
see the document (it misbehaves in the O column).

Actual Results:
1) =min and =mina is afraid of text cells
2) sometimes assigns strange values (totally non-existant in the selected range).

Expected Results:
1) =min() ignores text cells
2) =min() assigns the minimum value of the range it applies to.

Reproducible: Always

User Profile Reset: Yes

Additional Info:
Версия: (x86)
ID сборки: a726b36747cf2001e06b58ad5db1aa3a9a1872d6
Потоков ЦП: 2; ОС: Windows 6.1 Service Pack 1 Build 7601; Отрисовка ИП: по умолчанию; VCL: win; 
Локаль: ru-RU (ru_RU); Язык интерфейса: ru-RU
Calc: threaded
Comment 1 Roman Kuznetsov 2020-08-09 19:00:00 UTC
Created attachment 164078 [details]
File from description
Comment 2 Xisco Faulí 2020-08-10 17:22:38 UTC
I don't see any use of =min in column O. Could you please specify in which cell it is ?
Comment 3 Kęstutis Snieška 2020-08-10 19:21:04 UTC
Sorry, this one was reported in rush and against the Lord, so it is a mess.
   I've checked: 
      1) There are no formulas in column of the sheet "Lakštas1" ("Sheet1") (as You report).
      2) You may see the brokeness described in this report on the sheet "Lakštas1_2" ("Sheet1_2") -- essentially a copy of the first sheet for further manipulation of data. Weird results, which triggered my attention are in the rows 133 and 134.
Comment 4 Kęstutis Snieška 2020-08-10 19:30:40 UTC
Created attachment 164134 [details]
Modified attachment to showcase the situation

I refered to the original reported documents with links to my gooogle drive. Roman attached the first sheet of the "offending" document here, but the sheet lacked the =min() applied in the "O" column (the "O" column there was used for as a comment). I downloaded the attached file to this bug and applied the formula "=МИН(H{Row number}:N{Row number})to the column "O", saved and reatach it here.
Comment 5 Kęstutis Snieška 2020-08-10 19:47:21 UTC
The mistery of strange values is solved (J column was hidden, but the values was taken from it) (it is still hidden in the attached document):
   My Idea was as =min() is broken, probably =max() will be also. So I tried to apply it to the O colum and saw many huge values appearing (not visible at first). Then I noticed, that one column is missing, and made it appear. Now it is clear, where it came from. It also explains, how it got transfered to Google sheets and have the same behaviour.

   What's still a mistery, that functions =МИН(), =МИНА(), =МАКС(), =МАКСА() are "freaking" at text in cells in the range they are applied to.
Comment 6 Michael Warner 2020-08-10 22:50:42 UTC
It seems to me the reason you are getting #VALUE! in the O column, is because the range given to the min() formula contains the J column, which also has #VALUE!. The #VALUE! in the J column, is due to an arithmetic formula like this:

Where the corresponding cell in the I column has text. So, Calc is trying to find, for example, '20/nėra pozicijos*100' and getting an error. 

This all looks like expected behavior to me.