Date: prev next · Thread: first prev next last
2013 Archives by date, by thread · List index

Another trick that occurred to me when viewing that spreadsheet was
this one: Some of the column headers contain values that are needed in
calculations. Say for instance you have a column header in cell A1 that
reads "Max units 250". You can extract the value (250) with


Or, you can simply put 250 in the cell, and custom format it with this
format string:

"Max units "###

This way it shows as "Max units 250", but the cell contents is just
250, and can be used in calculations.

Neat trick.


P.S. Note that there is an issue across saves when using this trick to
format a number as a per hour rate, at least in the South African
locale. There's been a previous thread about this, and I still need to
file a bug report. Basically if you use a cell format like

"R "#,##0.00 " / hour"

It will be fine until you save, but things get messed up when you
reopen your document.

On Mon, 21 Oct 2013 15:19:30 +0200
Paul <> wrote:

Let me rather put it this way: I don't think using VLOOKUP solves the
underlying problem, which is that too much is being done in one

For any complicated formula, I feel the best solution is either a
custom written user function, or intermediate steps in columns. Hide
those columns, or put them on another worksheet if you don't want to
see them, but they should still be there. Makes things much easier.


On Mon, 21 Oct 2013 15:04:34 +0200
Paul <> wrote:

On Mon, 21 Oct 2013 14:32:40 +0200
"Hylton Conacher (ZR1HPC)" <> wrote:

appreciated especially as he also indicates that using VLOOKUP
would not work, in this case. MIN and MAX are still under testing
here :)
Brian indicated that VLOOKUP would work; I don't say different, I'm
just not sure how in this situation. I'm not that familiar with
VLOOKUP though, so it may not be that difficult, but it seems like
it would still be complicated. I still feel that the best approach
is to put columns for intermediate steps; VLOOKUP might make more
sense after that.

I am however still waiting for someone to indicate to me which
part of the formula in the Formula Wizard the boxes apply i.e.
what is the difference between the 'Result' and 'Function Result'
answer box.
The "Result" box shows the result of the complete formula for the
cell, while the "Function Result" box shows the result of just the
function currently under the cursor. For example, let's say you have
typed "=SQRT(SUM(A1:A2))", and A1 was 4 and A2 was 5. If you put
your cursor on "SQRT", both boxes would hold the answer 3, as that
is both the final result of the formula, and the result of
"SQRT(9)". If, however, you put your cursor on "SUM", then the
"Result" box would still show 3, as that is still the result of the
complete formula, but the "Function Result" box would now hold 9,
as that is the result of "SUM(A1:A2)".


To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.