Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenTue, 24 Dec 2019 19:15:03 +0100How to do a column sum when the cells are not numeric formattedhttps://ask.libreoffice.org/en/question/148569/how-to-do-a-column-sum-when-the-cells-are-not-numeric-formatted/ Hi,
I want to do a column sum from an imported calc document about data usage. The data in the cells are like "1.38 MB", "870 Bytes", "25.6 KB" etc. Any help will be appreciated. Thanks for answers.Thu, 08 Mar 2018 11:13:12 +0100https://ask.libreoffice.org/en/question/148569/how-to-do-a-column-sum-when-the-cells-are-not-numeric-formatted/Comment by Lupp for <p>Hi,
I want to do a column sum from an imported calc document about data usage. The data in the cells are like "1.38 MB", "870 Bytes", "25.6 KB" etc. Any help will be appreciated. Thanks for answers.</p>
https://ask.libreoffice.org/en/question/148569/how-to-do-a-column-sum-when-the-cells-are-not-numeric-formatted/?comment=148582#post-id-148582**This is not a matter of the format set for the cells, but one of the recognition of text appended to the numeric part of the entries as a modifyier to the meaning.**
There is no ready-made tool for the task.Thu, 08 Mar 2018 13:19:04 +0100https://ask.libreoffice.org/en/question/148569/how-to-do-a-column-sum-when-the-cells-are-not-numeric-formatted/?comment=148582#post-id-148582Answer by Lupp for <p>Hi,
I want to do a column sum from an imported calc document about data usage. The data in the cells are like "1.38 MB", "870 Bytes", "25.6 KB" etc. Any help will be appreciated. Thanks for answers.</p>
https://ask.libreoffice.org/en/question/148569/how-to-do-a-column-sum-when-the-cells-are-not-numeric-formatted/?answer=148577#post-id-148577To sum numeric data provided in text form is not a problem:
Assuming the range `A2:A1001` `{=SUM(IFERROR(VALUE(A2:A1001);"NotNumeric"))}` entered for array-evaluation will do.
The factual task is therefor the implied one: **How can I convert the the text parts looking like units with SI-like prefixes to numeric factors?**
There recently was **[this thread](https://ask.libreoffice.org/en/question/146487)** addressing a related question concerning input.
In your case you first should replace the wrongly used SI prefixes to the **[correct](https://en.wikipedia.org/wiki/Binary_prefix)** ones: M by Mi, K by Ki ...
How to do it is explained in detail in **[this attached example](/upfiles/15205088821077102.ods)**. I did not try to cobble a formula for a one-cell-solution. However splitting over so many helper columns may seem too detailed as soon as the solution is understood.Thu, 08 Mar 2018 12:35:06 +0100https://ask.libreoffice.org/en/question/148569/how-to-do-a-column-sum-when-the-cells-are-not-numeric-formatted/?answer=148577#post-id-148577Comment by Jim K for <p>To sum numeric data provided in text form is not a problem: <br>
Assuming the range <code>A2:A1001</code> <code>{=SUM(IFERROR(VALUE(A2:A1001);"NotNumeric"))}</code> entered for array-evaluation will do. </p>
<p>The factual task is therefor the implied one: <strong>How can I convert the the text parts looking like units with SI-like prefixes to numeric factors?</strong> </p>
<p>There recently was <strong><a href="https://ask.libreoffice.org/en/question/146487">this thread</a></strong> addressing a related question concerning input. <br>
In your case you first should replace the wrongly used SI prefixes to the <strong><a href="https://en.wikipedia.org/wiki/Binary_prefix">correct</a></strong> ones: M by Mi, K by Ki ... <br>
How to do it is explained in detail in <strong><a href="/upfiles/15205088821077102.ods">this attached example</a></strong>. I did not try to cobble a formula for a one-cell-solution. However splitting over so many helper columns may seem too detailed as soon as the solution is understood.</p>
https://ask.libreoffice.org/en/question/148569/how-to-do-a-column-sum-when-the-cells-are-not-numeric-formatted/?comment=149092#post-id-149092@velizet: If this answer was helpful then please mark it as correct. See [guidelines for asking](https://ask.libreoffice.org/en/question/137128/guidelines-for-asking/#138301).Tue, 13 Mar 2018 15:29:16 +0100https://ask.libreoffice.org/en/question/148569/how-to-do-a-column-sum-when-the-cells-are-not-numeric-formatted/?comment=149092#post-id-149092Comment by velizet for <p>To sum numeric data provided in text form is not a problem: <br>
Assuming the range <code>A2:A1001</code> <code>{=SUM(IFERROR(VALUE(A2:A1001);"NotNumeric"))}</code> entered for array-evaluation will do. </p>
<p>The factual task is therefor the implied one: <strong>How can I convert the the text parts looking like units with SI-like prefixes to numeric factors?</strong> </p>
<p>There recently was <strong><a href="https://ask.libreoffice.org/en/question/146487">this thread</a></strong> addressing a related question concerning input. <br>
In your case you first should replace the wrongly used SI prefixes to the <strong><a href="https://en.wikipedia.org/wiki/Binary_prefix">correct</a></strong> ones: M by Mi, K by Ki ... <br>
How to do it is explained in detail in <strong><a href="/upfiles/15205088821077102.ods">this attached example</a></strong>. I did not try to cobble a formula for a one-cell-solution. However splitting over so many helper columns may seem too detailed as soon as the solution is understood.</p>
https://ask.libreoffice.org/en/question/148569/how-to-do-a-column-sum-when-the-cells-are-not-numeric-formatted/?comment=148688#post-id-148688Thanks LuppFri, 09 Mar 2018 08:40:26 +0100https://ask.libreoffice.org/en/question/148569/how-to-do-a-column-sum-when-the-cells-are-not-numeric-formatted/?comment=148688#post-id-148688Answer by haegar2020 for <p>Hi,
I want to do a column sum from an imported calc document about data usage. The data in the cells are like "1.38 MB", "870 Bytes", "25.6 KB" etc. Any help will be appreciated. Thanks for answers.</p>
https://ask.libreoffice.org/en/question/148569/how-to-do-a-column-sum-when-the-cells-are-not-numeric-formatted/?answer=222525#post-id-222525Hi together,
had just the same problem: Data from the ISP was in KB MB GB mixed up all together, like this
25 KB
311 KB
3,66 MB
34 KB
9,32 MB
3,03 MB
1,23 MB
3,47 MB
300 KB
49 KB
3,47 MB
So before I can start here to count together, how much Bytes I daily spent on internet, I had to convert the whole row.
I thought this problem must be a common and maybe already solved one, and yes I found that:
https://www.mrexcel.com/board/threads/convert-mb-gb-kb-to-bytes.878293/
...good man: =LEFT(A1,FIND(" ",A1))*1024^MATCH(RIGHT(A1,2),{"KB","MB","GB"},0)
...for M$Excel... So for Libre not directly usable: as in my case (for whatever reason I ever decided to use a german version of libreoffice...) I also had to figure out the right german names for the functions, but now I managed it and want to share it with you :-) :
=LINKS(A11;FINDEN(" ";A11))*1024^VERGLEICH(RECHTSB(A11;2);{"KB";"MB";"GB"};0)
25 KB 25600
311 KB 318464
3,66 MB 3837788,16
34 KB 34816
9,32 MB 9772728,32
3,03 MB 3177185,28
1,23 MB 1289748,48
3,47 MB 3638558,72
300 KB 307200
49 KB 50176
3,47 MB 3638558,72
TATA! :-)
...it souldn't be too hard to convert this into english... I am so happy after hours of work!
The hardest point was, that the character between last number and first letter ("K","M","G") was not a space...
but I haven't figured out which character it was: with the RECHTSB ("RIGHTB"?) function I got the last two characters for the VERGLEICH (MATCH) so I didn't investigate any longer ;-)
Enjoy, because we care and share!
brgds
stefan
Austria, Vienna ...and no snow on christmas eve...Tue, 24 Dec 2019 18:07:12 +0100https://ask.libreoffice.org/en/question/148569/how-to-do-a-column-sum-when-the-cells-are-not-numeric-formatted/?answer=222525#post-id-222525Comment by Mike Kaganski for <p>Hi together,
had just the same problem: Data from the ISP was in KB MB GB mixed up all together, like this</p>
<pre><code>25 KB
311 KB
3,66 MB
34 KB
9,32 MB
3,03 MB
1,23 MB
3,47 MB
300 KB
49 KB
3,47 MB
</code></pre>
<p>So before I can start here to count together, how much Bytes I daily spent on internet, I had to convert the whole row.
I thought this problem must be a common and maybe already solved one, and yes I found that:</p>
<pre><code>https://www.mrexcel.com/board/threads/convert-mb-gb-kb-to-bytes.878293/
...good man: =LEFT(A1,FIND(" ",A1))*1024^MATCH(RIGHT(A1,2),{"KB","MB","GB"},0)
</code></pre>
<p>...for M$Excel... So for Libre not directly usable: as in my case (for whatever reason I ever decided to use a german version of libreoffice...) I also had to figure out the right german names for the functions, but now I managed it and want to share it with you :-) :</p>
<pre><code>=LINKS(A11;FINDEN(" ";A11))*1024^VERGLEICH(RECHTSB(A11;2);{"KB";"MB";"GB"};0)
25 KB 25600
311 KB 318464
3,66 MB 3837788,16
34 KB 34816
9,32 MB 9772728,32
3,03 MB 3177185,28
1,23 MB 1289748,48
3,47 MB 3638558,72
300 KB 307200
49 KB 50176
3,47 MB 3638558,72
</code></pre>
<p>TATA! :-)</p>
<p>...it souldn't be too hard to convert this into english... I am so happy after hours of work! </p>
<p>The hardest point was, that the character between last number and first letter ("K","M","G") was not a space...</p>
<p>but I haven't figured out which character it was: with the RECHTSB ("RIGHTB"?) function I got the last two characters for the VERGLEICH (MATCH) so I didn't investigate any longer ;-)</p>
<p>Enjoy, because we care and share!</p>
<p>brgds</p>
<p>stefan </p>
<p>Austria, Vienna ...and no snow on christmas eve...</p>
https://ask.libreoffice.org/en/question/148569/how-to-do-a-column-sum-when-the-cells-are-not-numeric-formatted/?comment=222533#post-id-2225331. To use English function names, just check "Use English function names" under Options-LibreOffice Calc-Formula. The formula is directly usable for LO; and for localized Excel, the same translation would be necessary.
2. KB, MB, etc are not multiples of 1024; they are multiples of 1000. KiB, MiB, etc are multiples of 1024.Tue, 24 Dec 2019 19:15:03 +0100https://ask.libreoffice.org/en/question/148569/how-to-do-a-column-sum-when-the-cells-are-not-numeric-formatted/?comment=222533#post-id-222533Answer by Mark McLean for <p>Hi,
I want to do a column sum from an imported calc document about data usage. The data in the cells are like "1.38 MB", "870 Bytes", "25.6 KB" etc. Any help will be appreciated. Thanks for answers.</p>
https://ask.libreoffice.org/en/question/148569/how-to-do-a-column-sum-when-the-cells-are-not-numeric-formatted/?answer=149012#post-id-149012No Macro's just formula's! Will sum cells with # with or without a decimal and with or without text(s) at right of #'s. 1gb, 1g, 1 gb, 1.11gb etc. [C:\fakepath\Sum Numbers having text at right.ods](/upfiles/15209295408342136.ods)Mon, 12 Mar 2018 18:24:28 +0100https://ask.libreoffice.org/en/question/148569/how-to-do-a-column-sum-when-the-cells-are-not-numeric-formatted/?answer=149012#post-id-149012Comment by m.a.riosv for <p>No Macro's just formula's! Will sum cells with # with or without a decimal and with or without text(s) at right of #'s. 1gb, 1g, 1 gb, 1.11gb etc. <a href="/upfiles/15209295408342136.ods">C:\fakepath\Sum Numbers having text at right.ods</a></p>
https://ask.libreoffice.org/en/question/148569/how-to-do-a-column-sum-when-the-cells-are-not-numeric-formatted/?comment=149038#post-id-149038You have enough karma to attach a file here, edit your question an use the 'clip' icon to attach a file.Mon, 12 Mar 2018 23:46:36 +0100https://ask.libreoffice.org/en/question/148569/how-to-do-a-column-sum-when-the-cells-are-not-numeric-formatted/?comment=149038#post-id-149038Comment by m.a.riosv for <p>No Macro's just formula's! Will sum cells with # with or without a decimal and with or without text(s) at right of #'s. 1gb, 1g, 1 gb, 1.11gb etc. <a href="/upfiles/15209295408342136.ods">C:\fakepath\Sum Numbers having text at right.ods</a></p>
https://ask.libreoffice.org/en/question/148569/how-to-do-a-column-sum-when-the-cells-are-not-numeric-formatted/?comment=149049#post-id-149049Using regular expressions can be done with only one formula `=SUMPRODUCT(VALUE(LEFT($O$7:$O$22;SEARCH("([^0-9\.\,]|$)";$O$7:$O$22;1)-1)))`, maybe your file was an excel file it has wildcards enable, not regular expressions, and formula is local seeting dependant..Tue, 13 Mar 2018 02:19:11 +0100https://ask.libreoffice.org/en/question/148569/how-to-do-a-column-sum-when-the-cells-are-not-numeric-formatted/?comment=149049#post-id-149049Comment by Mark McLean for <p>No Macro's just formula's! Will sum cells with # with or without a decimal and with or without text(s) at right of #'s. 1gb, 1g, 1 gb, 1.11gb etc. <a href="/upfiles/15209295408342136.ods">C:\fakepath\Sum Numbers having text at right.ods</a></p>
https://ask.libreoffice.org/en/question/148569/how-to-do-a-column-sum-when-the-cells-are-not-numeric-formatted/?comment=149063#post-id-149063Thanks @m.a.riosv! I spent a lot of time trying to put a link on it! Didn't know I could attach, having tried if before, (with less karma)!Tue, 13 Mar 2018 09:31:12 +0100https://ask.libreoffice.org/en/question/148569/how-to-do-a-column-sum-when-the-cells-are-not-numeric-formatted/?comment=149063#post-id-149063