Function SMALL() appears to bomb out when some consecutive numbers occur

English
#1

Attached is a small sheet where the identical expression copied down row by row identifies an error when it encounters SOME consecutive numbers.
The errors occur in column V so the other cells are just referencing the error.
Small.ods (22.7 KB)

The “SOURCE” data is simply a MENU>Sheet>Fill cells>Fill Random Number …uniform Integer - Min 1 - Max 10 - NO Seed - Enable Rounding - Zero Decimals. Feel free to re-seed and verify that all data is generated by LO.

As you can see L6:M6 generates an error as do L7:M7 & L10:M10 but other consecutives at L2:M2 & L8:M8 which are both natural and reversed order don’t generate the error. In fact if L11:M11 is manually adjusted to match L2:M2 the error is generated on that row but not row 2.
Also, manually amending SOME of the numbers in F:J to create consecutive numbers will generate the error.

#2

Rookie!

B2 → =TEXTJOIN(" ";1;X2:AB2) pulldown

X2 → =TEXT(O2;"00") pull right|down

and yes, I can confirm the errors with LO6.1 on debian10…
update: The Errors occurs, because the Sourcecells contains Text instead Values ROOKIE!!

1 Like
#3

@karolus They all contain text ****Does MUPPET work ***** :rofl:

#4

The framed TEXT is generated by LO. The SMALL() function is the first operation upon that and it only fails some of the time. All the other errors are just reference errors which wouldn’t occur if LO hadn’t produced the processing errors. @karolus The two tips for text manipulation are appreciated. Thanks

#5

I dont believe you, and I claim the wrong formattings have been created before by you and not by Calc when generating the random numbers

2 Likes
#6

I’ve tried regenerating the random numbers, I’ve tried changing all the formats to standard format numbers but as you can see - all the random number cells are formatted identically - even if they are text. The error is still only occurring when SOME adjacent numbers are processed

#7

you may regenerate the randoms 1000times … but they do never delete and overwrite the existing hardformatted Textcells!!!

#8

Interesting new test. Select all the columns F:M and reformat them standard numbers with 1 decimal.
Reformat all the columns O:V and reformat them the same
The error persists

#9

I have reformatted all the cells to numerical many times to eliminate that possibility. The function works sometimes on some of the cells but never on some of the cells with consecutive numbers. All the source and destination cells are formatted numerical.

#10

Maybe, but I cant reproduce any Error here with LO6.1

#11

It appears that reformatting a range of cells to numerical doesn’t always reformet the cells. I just copied down working source numbers in the Random array and that had the effect of overwring/correcting the fatal text format… The errors have gone, the only problem now is that I have no idea how to recreate the symptoms to file a bug report on the fact that reformatting a range of cells doesn’t necessarily reformat the range in its entirety.
Another good day - learned more than two new things. Again, thanks for the text manipulation tips - I’ve got about 30000 cells to modify now :man_facepalming:

#12

never… copy one and paste it into whole CellRange

#13

That was my plan - I was being flippant and quantifying just how many places have the inferior expression
Update: And they’ve been like it since 2016 :thinking:

#14

Most common spreadsheet misconception since 1980, explained ad nauseam in every book, tutorial, script on any kind of spreadsheet software, millions of times in mailing lists, news groups, forums etc.

A cell value IS either text or it IS a number regardless of any formatting attribute. Just like you won’t change the type of value by applying a color or font, the number format must never change any value.
Number format “Text” (code @) influences how NEW input is handled without having any effect on existing values. Anything entered into a preformatted text cell will be treated as literal text. 01234 is a text that consists of digits only. It is not equal to the number 1234. =SUM(A1:B5) is a text with a leading equal sign when entered into a preformatted text cell. Neither the digits nor the formula string will be evaluated when you switch to non-text format. Instead, both strings will show an additional apostrophe in the formula bar (whichis not part of the cell’s text) indicating that this text remains unevaluated.
Instead of preformatting, you can also type a leading apostroph when you want to enter a numeral or a formula expression as literal text. TRY OUT NOW with some completely unformatted sheet cells.

2 Likes
#15

@Villeroy Which may have been more use than an ashtray on a motorbike if you had read “All I did was random fill a range of cells with integers and then overtyped the obvious duplicated numbers to make each number in the row unique”. Which bit don’t you understand? Can I help you with some reading lessons? I can recommend DrZeuss.
I think you’ll also find if you go back and read that puerile list yourself you’ll discover that LibreOffice Calc defines every cell as numerical the moment the program is asked to create a new open document spreadsheet.

#16

Insulting people who are trying to help you will not help you.

I can only repeat: no matter how often you input numbers into a cell, if it was formatted as Text already then the cell content will be text not numeric. In your attached document the range F2:N14 is formatted as Text, hence any input there including random number generation will be text.

#17

Random fill seems to go in as numbers even when the cells are formatted as text. But if you then manually overwrite any of those numbers, then the new number will be entered as text. This appears to be what has happened.

You can test:

  1. Create a new spreadsheet and format cells A1:D4 as Text
  2. Keeping the selection, click Sheet > Fill Cells > Fill Random Number in dialogue box enter Uniform Integer, Minimum 0, Maximum 9 and OK
  3. Click View > Value Highlighting and see the numbers are blue for Number
  4. Enter a number anywhere in A1:D4 and click Enter. The replacement number changes colour to black showing that it is Text

This appears to be exactly what you have described that you have done, and what I see appears to have been done on the spreadsheet small.ods

You can fix your spreadsheet, turn on Value highlighting to see changes:

  1. Select your Text formatted purple outlined cells and format them as Number (General)
  2. Press Ctrl+H , in Find enter ^. in Replace enter &
  3. Tick the boxes Current Selection and Regular Expressions and then click Replace All

Cheers, Al

3 Likes
#18

If you didn’t consider @Villeroy 's opening sentence as exceedingly derogatory then I think you missed something. It certainly says more about Vill than anything else

#19

I am aware of that but normally AND always in Excel, Google sheets, if you type or copy a numerical construct into a previously “text occupied” cell it provides a correct mathematical result. That also worked for aeons with LO. I have just experimented again with all three packages and even inserting a column after a text column adopts the text format but still adapts to a mathematical construct. Yesterday LO wouldn’t comply but today it does. Now I recall an incident from yesterday where I selected five columns down and “filled” them with a simple formula only to realise my mistake in targeting 5m+ cells and was forced to “end task” in Task Manager. Fine, upon reloading LO it still insisted upon recovering the old file even though I said “NO”. I exited without saving and then reloaded the “original”. That’s probably where the issue lies. I can only suspect LO found something it had left “memory resident” which was impacting the procedures until I finally “switched off” last night.
This raises two issues:-
Why does LO ignore my instructions to not recover a failed document?
Why is there no algorithm that interprets the current size/processing profile of the active sheet, interprets what is a reasonable operational response time, periodically clears the keyboard buffer and scans for an ESC if the current process seems unreasonably long? Instead of just flagging up “Libre office is not responding”. Believe it or not, the user generally knows and is desperately searching for a way to “kill” a rogue process.

#20

Thank you @EarnestAl for an adequate and accurate response to a user needing help instead of denigration. As I mentioned in my response to @erAck - I think crashing out of the file corrupted something for the duration of my computer session. Today’s experiments along similar lines to your suggestions were indeed successful. It even proved that inserting a column next to a text column can still accept a mathematical construct, even though it inherited “text” characteristics from the preceding column.

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