Prev: [discuss] THANK YOU
Next: Open Office problem
From: Andreas Saeger on 17 Feb 2010 15:17
Luz Zdee wrote:

> What have my longtime fvorite OO makes so dumb and down?
>


Hard formatting and inefficient lookups.


---------------------------------------------------------------------
To unsubscribe, e-mail: discuss-unsubscribe(a)openoffice.org
For additional commands, e-mail: discuss-help(a)openoffice.org

From: lutzdietrich on 17 Feb 2010 18:23
Am 17.02.2010, 21:17 Uhr, schrieb Andreas Saeger <saegerei(a)onlinehome.de>:

Hallo, thanks for the fast response.
But how to prevent formattings an inefficient lookups?

The same Spreadsheet, exported in XLS format arises from 1Mb to 12Mb.
Additional the Load/Savetime decreases from 4 Minutes (OO) to 30seconds
(XLS).
Tha is an advantage of 1:80 to M$.
Thats why i think must be an kind of expansion.

However:
now i own the Excel, and i must reuse all handlings from beginning......
It looks terrible, works unsorted and nagged with many useless features...
I want to return to OO, but dont know how overcame this issues.

best regards

Luz Zdee

> Luz Zdee wrote:
>> What have my longtime fvorite OO makes so dumb and down?
>>
> Hard formatting and inefficient lookups.
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: discuss-unsubscribe(a)openoffice.org
> For additional commands, e-mail: discuss-help(a)openoffice.org

---------------------------------------------------------------------
To unsubscribe, e-mail: discuss-unsubscribe(a)openoffice.org
For additional commands, e-mail: discuss-help(a)openoffice.org

From: Andreas Saeger on 18 Feb 2010 06:01
Lutz Dietrich (public) wrote:
> Am 17.02.2010, 21:17 Uhr, schrieb Andreas Saeger <saegerei(a)onlinehome.de>:
>
> Hallo, thanks for the fast response.
> But how to prevent formattings an inefficient lookups?
>

This performs a lookup twice for one result:
=IF(ISNA(VLOOKUP(A1;range;1;0));"";VLOOKUP(A1;range;1;0))

This performs the lookup in the first column of "range" 3 times:
X1: =VLOOKUP(A1;range;1;0)
Y1: =VLOOKUP(A1;range;2;0)
Z1: =VLOOKUP(A1;range;3;0)

Lookup once and return many values at once:
X1: =MATCH(A1;vector;0)
Y1:AA1: INDEX(range;X1;0) [Ctrl+Shift+Enter]

MATCH works pretty fast with many thousands of values in ordered mode
(last argument 1 or missing):
X1: =MATCH(A1;vector;1)
Y1: =A1=INDEX(vector;X1) [test for exact match]
Z1:AB1: =IF(Y1;INDEX(range;X1;0);NA()) [Ctrl+Shift+Enter]

Excel seems to maintain some internal cache for redundant calculations.
If I recall correctly, information about the calculation cascade is
part of the very complicated Excel file formats.
ODF does not keep anything like that, which makes it easy to implement
but rather inefficient.

Use a relational database with millions of interrelated records and
never look back to spreadsheets.


---------------------------------------------------------------------
To unsubscribe, e-mail: discuss-unsubscribe(a)openoffice.org
For additional commands, e-mail: discuss-help(a)openoffice.org

From: lutzdietrich on 2 Mar 2010 06:52

Hallo Andreas, sorry for the late reply. I hunk may you are right. I am using multiple * HLOOKUP and VLOOKUP.
Otherhands i have experiences in INDEX in combination with HLOOKUP and WLOOKUP. With i can mirroring an Matrix. So i can read left from criteria.
But never i recognized that these constructs cause such trouble.
Thank you very much for your kindly service
Lutz
Andreas Saeger geschrieben am 18.2.10 12:01

>Lutz Dietrich (public) wrote:
>> Am 17.02.2010, 21:17 Uhr,
>schrieb Andreas Saeger
><saegerei(a)onlinehome.de>:
>>
>> Hallo, thanks for the fast
>response.
>> But how to prevent
>formattings an inefficient
>lookups?
>>
>
>This performs a lookup twice
>for one result:
>F(ISNA(VLOOKUP(A1;range;
>1;0));"";VLOOKUP(A1;range
>;1;0))
>
>This performs the lookup in
>the first column of "range" 3
>times:
>X1: LOOKUP(A1;range;1;0)
>Y1: LOOKUP(A1;range;2;0)
>Z1: LOOKUP(A1;range;3;0)
>
>Lookup once and return
>many values at once:
>X1: ATCH(A1;vector;0)
>Y1:AA1: INDEX(range;X1;0)
>[Ctrl+Shift+Enter]
>
>MATCH works pretty fast
>with many thousands of
>values in ordered mode
>(last argument 1 or missing):
>X1: ATCH(A1;vector;1)
>Y1: ¡NDEX(vector;X1) [test
>for exact match]
>Z1:AB1:
>F(Y1;INDEX(range;X1;0);NA(
>)) [Ctrl+Shift+Enter]
>
>Excel seems to maintain
>some internal cache for
>redundant calculations.
> If I recall correctly,
>information about the
>calculation cascade is
>part of the very complicated
>Excel file formats.
>ODF does not keep anything
>like that, which makes it
>easy to implement
>but rather inefficient.
>
>Use a relational database
>with millions of interrelated
>records and
>never look back to
>spreadsheets.
>
>
>-----------------------------
>-----------------------------
>-----------
>To unsubscribe, e-mail:
>discuss-
>unsubscribe(a)openoffice.org
>For additional commands, e-
>mail: discuss-
>help(a)openoffice.org



---------------------------------------------------------------------
To unsubscribe, e-mail: discuss-unsubscribe(a)openoffice.org
For additional commands, e-mail: discuss-help(a)openoffice.org

 | 
Pages: 1
Prev: [discuss] THANK YOU
Next: Open Office problem