Re: [libreoffice-users] Help with LOCalc formula

At 17:58 07/11/2016 +0200, Pertti Rönnberg wrote:
> 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 below example simplified to 
> 5 events concerning 3 companies may clarify 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

Sorry, but why do you think that VLOOKUP() is not 
an option? As has already been suggested, it is 
exactly what you need. Are you perhaps concerned 
that column Z in your company table is so far 
away from column B? That does not matter. For the 
formulae in column X of your events table, you 
retrieve the required value from the company 
table using VLOOKUP(). Its first parameter is the 
company name in the events table, Bn or whatever. 
The second parameter is the range that contains 
the company names and values, so B$20:Z$22 (if 
you see what I mean). The third parameter is the 
column in that range containing the required 
value; if the range really does span columns B to 
Z, that will be 25. The fourth parameter should 
be FALSE (or zero), to indicate that the company 
names in column B of the company table are not sorted.

I trust this helps.

Brian Barker


--
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