Re: [libreoffice-users] Help with LOCalc formula (SOLVED)

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