2016 Archives by date, by thread · List index

# Re: [libreoffice-users] Help with LOCalc formula

```Try this.

=IF(B1="","",OFFSET(\$Z\$20,MATCH(B1,\$B\$20:\$B\$35,0)-1,25))

--
Jim

-----Original Message-----
From: "Pertti Rönnberg" <ptjr@elisanet.fi>
To: "users@global.libreoffice.org" <users@global.libreoffice.org>
Cc: "Pertti Rönnberg" <ptjr@elisanet.fi>
Sent: Mon, 07 Nov 2016 8:00
Subject: [libreoffice-users] Help with LOCalc formula

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

Description
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
order.
r(2) sums the up-to-date result per company and the total result.

Problem
The belowe example simplified to 5 events concerning 3 companies may
clearify the problem with the formula in X1 copied down col.X:
=if(B1=\$B\$20;\$Z\$20;if(B1=\$B\$21;\$Z\$21;(ifB1=\$B\$22;\$Z\$22;0)))

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.
Pertti Rönnberg/Finland

--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
```

### Context

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.