2016 Archives by date, by thread · List index

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

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 <ptjr@elisanet.fi> wrote:

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-uns
ubscribe/
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

• Re: [libreoffice-users] Help with LOCalc formula · Bruce Hohl