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

I think VLOOKUP might work. In cell X1 try this:  =VLOOKUP(B1,B$20:C$22,2,0)
Copy down as needed; Adjust 2nd parameter (B$20:C$22) as needed.

On Mon, Nov 7, 2016 at 10:58 AM, Pertti Rönnberg <> wrote:

Best Spreadsheet Experts,
May I ask for help having a more elegant formula in LOCalc (v.5.0.3; win10)

I try to get a little extra to my pension savings by experimenting on the
share market and follow up the results using Calc (not a database).
I have a spreadsheet with two ranges – range(1) directly above range(2).

r(1) specifies per row every specific purchase/sell event (date, company
name, amount, etc).
To calculate the actual "to-day"-result for this specific event (row) we
need the "to-day"-value for this company's share (in e.g. column X), which
value is taken from respective company's row in col.Z in r(2).
r(1) has yearly 100-130 rows (events)

Range(2) lists each company named in the above Range(1) and has in col.Z
the corresponding share's "to-day"-value. These inserted share values
varies from one day to the next.
There are now 10-15 companies (rows) listed in r(2) – not in alphabetic
r(2) sums the up-to-date result per company and the total result.

The belowe example simplified to 5 events concerning 3 companies may
clearify the problem with the formula in X1 copied down col.X:

As you can see: with 15 (or more) companies (and >100 events) the formula
will grow and become very long and sensitive for mistakes.
VLOOKUP does not seem to be an option, at least it requires a completely
new setup of the page.

Range(1) - events
A       B C….                 X
1.     nokia                       5,o
2.     kone                     41,o
3.     nokia                       5,o
4.     fiskars                         12,o
5.     kone                        41,o

Range(2) - companies
A         B C...         Z
20     nokia               5,o
21     fiskars               12,o
22     kone             41,o

Any suggestion will be very interesting.
Thank you in advance
Pertti Rönnberg/Finland

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

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.