At 17:38 04/08/2014 +0200, Daniel R. Miguel wrote:
I have a table with cells that may contain OTnum, so it might
contain OT0.5, OT1, OT2, OT2.5 et cetera. I would want to sum the
number substring of all the cells in a row, so for example if I have this row:
Alan OT0,5 F OT1 F V OT2 F F V OT3
I would have another column with the total sum of OT:
Alan OT0,5 F OT1 F V OT2 F F V OT3 6,5
(OT3+OT2+OT1+OT0.5)
Suppose those eleven items are cells A1 to K1. In the result cell, enter
=SUM(IF(LEFT(B1:K1,2)="OT",VALUE(MID(B1:K1,3,99)),0))
But don't press Enter or click the green tick mark; instead press
Ctrl+Shift+Enter to render it an array formula. You will see that the
entire formula displayed in the Input Line has been surrounded by
braces - { } - but note that you cannot achieve the same result by
typing these yourself.
You will presumably need to copy this formula down a column. If so,
you appear to be able to achieve the correct result by copying the
formula from the first cell and pasting it into others (even
wholesale) or by using Edit | Fill, but not by dragging the fill handle.
I trust this helps.
Brian Barker
--
To unsubscribe e-mail to: [email protected]
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
