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


Hi,
Bruce, Remy, Brian and Jim
Thank you for your kindness and answers.

Tonight while following the election on TV I'll have a lot to do when giving the VLOOKUP() a new try according to your advice.
I must have done bad thinking -- perhaps getting old.
Anyway I again learn a lot.
Best regards
Pertti


On 8.11.2016 11.58, James E Lang wrote:
Correction: replace 25 with 0.


--
Jim

-----Original Message-----
From: "James E. Lang" <jim+lou@lang.hm>
To: "users@global.libreoffice.org" <users@global.libreoffice.org>, "Pertti Rönnberg" <ptjr@elisanet.fi>
Cc: "Pertti Rönnberg" <ptjr@elisanet.fi>
Sent: Tue, 08 Nov 2016 1:52
Subject: 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 <mailto:ptjr@elisanet.fi>>
To: "users@global.libreoffice.org <mailto:users@global.libreoffice.org>" <users@global.libreoffice.org <mailto:users@global.libreoffice.org>>
Cc: "Pertti Rönnberg" <ptjr@elisanet.fi <mailto:ptjr@elisanet.fi>>
Sent: Mon, 07 Nov 2016 8:00
Subject: [libreoffice-users] Help with LOCalc formula

Best Spreadsheet Experts,
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.
Thank you in advance
Pertti Rönnberg/Finland


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org <mailto: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 <mailto: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.