Re: [libreoffice-users] Sum of multiple cells substring

Hello again!

I realized the error was that the decimal point is a , (we are using 
Spanish localization), so changing the , for a . decimal point did the 
trick.

Once again, thank you a lot.
El 05/08/2014 a las #4, Daniel R. Miguel escribió:
> Hi again,
> I modified the formula to suit my spreadsheet to 
> {=SUM(IF(LEFT(B46:AE46,2)="OT",VALUE(MID(B46:AE46,3,99)),0))}. It 
> works beautifully but, in some instances it will render an Err:502.
> One of said instances is this:
> OT8 	HF8 	OT8 	OT2 	OT1 	OT0,5 	OT8 	OT8 	OT8 	OT5 	OT6 	OT5 	OT6 
> OT5 	OT5 	OT8 	OT5 	OT6 	OT3 	OT8 	OT8 	OT4 	OT8 	OT8 	OT5 	OT1 
> OT5 	OT4 	OT8 	OT8
>
>
> Is the HF8 the one to blame?
>
> Thanks again.
>
> El 05/08/2014 a las #4, Brian Barker escribió:
> > 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: 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