Le lundi 07 novembre 2016 à 17:58 +0200, Pertti Rönnberg a écrit :
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)))
Unless there is something I do not understand, VLOOKUP() would probably
be a good option for you. I would enter the formula as follows (based
on your example) in X1:
=IFERROR(VLOOKUP(B1;B$20:Z$22;25;FALSE());0)
The "IFERROR()" is used to return a value of zero in case the lookup
fails, which corresponds to the last selection when false in the string
of nested IF() functions you listed.
You can also re-write the formula like this:
=IFERROR(VLOOKUP(B1;OFFSET(B$20;0;0;<number_of_entries>;25);25;FALSE())
;0)
where <number_of_entries> is replaced by a formula that counts the
number of entries you have in r(2). This way, as you add entries, the
formula dynamically adjusts itself to the correct count of values and
you can drag this formula with little risk of error; depending on what
you are counting COUNT() or COUNTA() would work for you. Additionally,
you could assign a name to cell $B$20 and use that name in the formula
instead of an explicit reference to the cell.
Alternatively, you could use the MATCH() function to locate on which
row the company name is and then use INDIRECT(ADDRESS()) to retrieve
the value you are looking for. This is slightly more complex and in my
mind does not really perform better than VLOOKUP() in this case, but it
remains an alternative.
I hope this helps.
Rgds,
Rémy Gauthier.
Any suggestion will be very interesting.
Thank you in advance
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
Context
- Re: [libreoffice-users] Help with LOCalc formula · Remy Gauthier
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.