Keeping number format when accesing that cell value from another cell

[vizoso]

Sorry for the messy title but I don’t know a better way of summing it. The issue is the following: I have some numbers in my calc document that are bounds, so they have a “<” symbol in front of them. I have that symbol set in their number format (as "<"0.0##) so I can still make operations with them. Then, in another place, I have this code:

=INDEX(A1:A100,COUNTA(A1:A100),1)

which basically shows the last cell value on the column A. Except it doesn’t maintain the number format and I lose the “<” sign! Does anyone know a way to fix this?

[anon73440385]

You need to format the cell containing your formula the same way as the originating (referred) cell. Beware: Formulas don’t copy anything, thus stating doesn’t maintain the number format in fact is a statement which makes no sense in itself. Formulas refer to some values (content!!) of other cells, but don’t perform any magic copy of the content of a cell to the target cell containing the formula.

[vizoso]

The issue is that in that column I have some numbers with that format and some numbers with other, so it’s not like I can change the format of that cell (it would kind of defeat the purpose of the index formula. I thought that there should be something because when you do “=A1” (for example) it doesn’t only copy the value inside A1, it also copies the number format…

[anon73440385]

cause when you do “=A1” (for example) it doesn’t only copy the value inside A1, it also copies the number format

No, wrong - LibreOffice recognizes that the value referenced by the formula is a number (and in some cases changes the format of the referencing cell [e.g. when dealing with dates] automatically). Again - references don’t copy anything. The content of the cell containing the formula keeps to be the formula; otherwise it would only work once. Your format specification even doesn’t add a “<” to the content of your cells in column A, but just shows that character - the content doesn’t change. Thus you even can’t use some simple if cell contains “<” character then … else …-logic (in conjunction with STYLE() function).

[keme]

As indicated in comments, there is no way for a formula to replicate cell formatting of a referenced cell.
If you enter the operator with the number, you can still extract the number by utilizing string manipulation functions, but using summary functions (sum, average, lookup, etc.) will be difficult.

The best workaround I can see is to use two cells. One for the operator (cell formatted as text) and the next for the number.

[vizoso]

Thanks to everyone for the help! after some days of thinking I came up with a neat solution (I already tried the one from keme but it was a bit cumbersome to add several cells to the excel, it’s not a small sheet). Since I’m only working with positive numbers and those can either have the < symbol or not, and the index function won’t pick up the style, it meant that for it to be automatic I had to create only one number style that I can apply to all columns (the original ones and the ones that have the index reference function). That style is:

General;"<"General

That way if it is a bound (has < symbol) I just make the number negative. Otherwise, It will be shown as a regular number. This method of course fails when adding different numbers but that’s not something that I have to do (I only apply factors to change units and stuff) so it should be fine.