sum text column of common unit of measure numbers

classic Classic list List threaded Threaded
14 messages Options
bjlockie bjlockie
Reply | Threaded
Open this post in threaded view
|

sum text column of common unit of measure numbers

My cell phone provider only provides me a CSV with a text field for the
usage.
I want to =sum( it so I need it to be numbers.
Is a macro the easiest way to make a new column of common unit of
measure (GB?) numbers?

15 KB
5 KB
10 KB
15 KB
5 KB
5 KB
5 KB
1.8 MB
5 KB
5 KB
15 KB
215 KB
130.2 MB
9.2 MB
475 KB
11.7 MB
10 MB
15 KB
12.1 MB
5 KB
10 KB
7.1 MB
5.3 MB
4.9 MB
15 KB
240 KB
130 MB
2.9 MB


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Malcolm Jaros Malcolm Jaros
Reply | Threaded
Open this post in threaded view
|

Fwd: [libreoffice-users] sum text column of common unit of measure numbers

Hi James

>My cell phone provider only provides me a CSV with a text field for the
>usage.
>I want to =sum( it so I need it to be numbers.
>Is a macro the easiest way to make a new column of common unit of
>measure (GB?) numbers?
>
>15 KB
>5 KB
>10 KB
>15 KB
>5 KB
>5 KB
>5 KB
>1.8 MB
>5 KB
>5 KB
>15 KB
>215 KB
>130.2 MB
>9.2 MB
>475 KB
>11.7 MB
>10 MB
>15 KB
>12.1 MB
>5 KB
>10 KB
>7.1 MB
>5.3 MB
>4.9 MB
>15 KB
>240 KB
>130 MB
>2.9 MB

I suggest the following procedure.
Open the *.TXT file in CALC, ticking "Separated by" and "Space" boxes. Should produce spreadsheet with Numeric values in Column A and Text (either MB or KB) in column B.
In Cell C1 put =IF(B1="MB",A1*1000,A1)
Copy Cell C1 down to bottom of table.
Save as *.odt file

Regards, MalJaros

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Malcolm Jaros Malcolm Jaros
Reply | Threaded
Open this post in threaded view
|

Re: sum text column of common unit of measure numbers

Correction
Last line should read
Save as *.ods file
Regards, MalJaros

----- Original Message -----
From: "maljaros" <[hidden email]>
To: "Global Users@" <[hidden email]>
Sent: Thursday, 7 May, 2020 22:00:43
Subject: Fwd: [libreoffice-users] sum text column of common unit of measure numbers

Hi James

>My cell phone provider only provides me a CSV with a text field for the
>usage.
>I want to =sum( it so I need it to be numbers.
>Is a macro the easiest way to make a new column of common unit of
>measure (GB?) numbers?
>
>15 KB
>5 KB
>10 KB
>15 KB
>5 KB
>5 KB
>5 KB
>1.8 MB
>5 KB
>5 KB
>15 KB
>215 KB
>130.2 MB
>9.2 MB
>475 KB
>11.7 MB
>10 MB
>15 KB
>12.1 MB
>5 KB
>10 KB
>7.1 MB
>5.3 MB
>4.9 MB
>15 KB
>240 KB
>130 MB
>2.9 MB

I suggest the following procedure.
Open the *.TXT file in CALC, ticking "Separated by" and "Space" boxes. Should produce spreadsheet with Numeric values in Column A and Text (either MB or KB) in column B.
In Cell C1 put =IF(B1="MB",A1*1000,A1)
Copy Cell C1 down to bottom of table.
Save as *.odt file

Regards, MalJaros

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Wade Smart Wade Smart
Reply | Threaded
Open this post in threaded view
|

Re: sum text column of common unit of measure numbers

In reply to this post by bjlockie
Copy your data into a txt file. Open with your
spreadsheet, separate by space to put numbers
in Col A and Kb/mb in Col b. Col c =  A1*1000 to get
total kb. The sum up the whole thing. Divide the total
by 1000 to get mb.
--
Registered Linux User: #480675
Registered Linux Machine: #408606
Linux since June 2005

On Thu, May 7, 2020 at 2:05 PM James <[hidden email]> wrote:

>
> My cell phone provider only provides me a CSV with a text field for the
> usage.
> I want to =sum( it so I need it to be numbers.
> Is a macro the easiest way to make a new column of common unit of
> measure (GB?) numbers?
>
> 15 KB
> 5 KB
> 10 KB
> 15 KB
> 5 KB
> 5 KB
> 5 KB
> 1.8 MB
> 5 KB
> 5 KB
> 15 KB
> 215 KB
> 130.2 MB
> 9.2 MB
> 475 KB
> 11.7 MB
> 10 MB
> 15 KB
> 12.1 MB
> 5 KB
> 10 KB
> 7.1 MB
> 5.3 MB
> 4.9 MB
> 15 KB
> 240 KB
> 130 MB
> 2.9 MB
>
>
> --
> To unsubscribe e-mail to: [hidden email]
> Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
bjlockie bjlockie
Reply | Threaded
Open this post in threaded view
|

Re: sum text column of common unit of measure numbers

In reply to this post by Malcolm Jaros
On 2020-05-07 4:17 p.m., Malcolm Jaros wrote:
> In Cell C1 put =IF(B1="MB",A1*1000,A1)
>
How can I have 2 IFs in case there are GBs?


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Wade Smart Wade Smart
Reply | Threaded
Open this post in threaded view
|

Re: sum text column of common unit of measure numbers

=if(B1="MB,A1*1000,A1*??)

You just change the final A1 to multiple by another number.

--
Registered Linux User: #480675
Registered Linux Machine: #408606
Linux since June 2005

On Thu, May 7, 2020 at 4:46 PM James <[hidden email]> wrote:

>
> On 2020-05-07 4:17 p.m., Malcolm Jaros wrote:
> > In Cell C1 put =IF(B1="MB",A1*1000,A1)
> >
> How can I have 2 IFs in case there are GBs?
>
>
> --
> To unsubscribe e-mail to: [hidden email]
> Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
remygauthier remygauthier
Reply | Threaded
Open this post in threaded view
|

Re: sum text column of common unit of measure numbers

In reply to this post by Wade Smart
Hi,
If your values takes up, for instance the range A2 to A29, you can
enter this formula as an array formula:
=SUM(VALUE(SUBSTITUTE(SUBSTITUTE(A2:A29," KB","")," MB","")))
The only trick is to hit CTRL-SHIFT-ENTER instead of just ENTER at the
end of the formula entry: if you only use ENTER, this will be a
"normal" formula and it will not work. To make sure you did it right,
the formula should appear with curly braces on each side in the formula
display, like this:
{=SUM(VALUE(SUBSTITUTE(SUBSTITUTE(A2:A29," KB","")," MB","")))}
Another thing toi be careful about: if your decimal separator is the
comma (","), the dot in your list of numbers will also have to be
changed. You can change the formula like this to make the change of
separator:
=SUM(VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2:A29," KB",""),"
MB",""),".",",")))
Now, if you really want to be fancy, you can use the REGEX function to
remove the units. This will remove KB, MB, and GB:
=SUM(VALUE(REGEX(A2:A29," [KMG]B","")))
And don't forget that CTRL-SHIFT !
I hope this helps.
Rémy.
Le jeudi 07 mai 2020 à 15:23 -0500, Wade Smart a écrit :

> Copy your data into a txt file. Open with yourspreadsheet, separate
> by space to put numbersin Col A and Kb/mb in Col b. Col c =  A1*1000
> to gettotal kb. The sum up the whole thing. Divide the totalby 1000
> to get mb.-- Registered Linux User: #480675Registered Linux Machine:
> #408606Linux since June 2005
> On Thu, May 7, 2020 at 2:05 PM James <[hidden email]> wrote:
> > My cell phone provider only provides me a CSV with a text field for
> > theusage.I want to =sum( it so I need it to be numbers.Is a macro
> > the easiest way to make a new column of common unit ofmeasure (GB?)
> > numbers?
> > 15 KB5 KB10 KB15 KB5 KB5 KB5 KB1.8 MB5 KB5 KB15 KB215 KB130.2 MB9.2
> > MB475 KB11.7 MB10 MB15 KB12.1 MB5 KB10 KB7.1 MB5.3 MB4.9 MB15 KB240
> > KB130 MB2.9 MB
> >
> > --To unsubscribe e-mail to:
> > [hidden email]
> > Problems?
> > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> > Posting guidelines + more:
> > https://wiki.documentfoundation.org/Netiquette
> > List archive: https://listarchives.libreoffice.org/global/users/
> > Privacy Policy: https://www.documentfoundation.org/privacy

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Wade Smart Wade Smart
Reply | Threaded
Open this post in threaded view
|

Re: sum text column of common unit of measure numbers

In reply to this post by Wade Smart
=IF(B1="kb",A3, IF(B1="mb",A1*1000,A1*1000000))


--
Registered Linux User: #480675
Registered Linux Machine: #408606
Linux since June 2005

On Thu, May 7, 2020 at 4:56 PM Wade Smart <[hidden email]> wrote:

>
> =if(B1="MB,A1*1000,A1*??)
>
> You just change the final A1 to multiple by another number.
>
> --
> Registered Linux User: #480675
> Registered Linux Machine: #408606
> Linux since June 2005
>
> On Thu, May 7, 2020 at 4:46 PM James <[hidden email]> wrote:
> >
> > On 2020-05-07 4:17 p.m., Malcolm Jaros wrote:
> > > In Cell C1 put =IF(B1="MB",A1*1000,A1)
> > >
> > How can I have 2 IFs in case there are GBs?
> >
> >
> > --
> > To unsubscribe e-mail to: [hidden email]
> > Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> > Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> > List archive: https://listarchives.libreoffice.org/global/users/
> > Privacy Policy: https://www.documentfoundation.org/privacy

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
remygauthier remygauthier
Reply | Threaded
Open this post in threaded view
|

Re: sum text column of common unit of measure numbers

Hello,
I just realised I messed up since I did not think about the
units.Considering:
1 GB = 1024 MB1 MB = 1024 KB
you can change the formula like this:
=SUM(IFERROR(VALUE(SUBSTITUTE(A2:A29,"
KB","")),0),IFERROR(VALUE(SUBSTITUE(A2:A29,"
MB",""));0)*1024,IFERROR(VALUE(SUBSTITUE(A2:A29,"
GB",""));0)*1024*1024)
This will give you the number of KB transferred. If you want the total
in MB, just divide the SUM by 1024.
I hope this helps,
Rémy.

Le jeudi 07 mai 2020 à 17:09 -0500, Wade Smart a écrit :

> =IF(B1="kb",A3, IF(B1="mb",A1*1000,A1*1000000))
>
> -- Registered Linux User: #480675Registered Linux Machine:
> #408606Linux since June 2005
> On Thu, May 7, 2020 at 4:56 PM Wade Smart <[hidden email]>
> wrote:
> > =if(B1="MB,A1*1000,A1*??)
> > You just change the final A1 to multiple by another number.
> > --Registered Linux User: #480675Registered Linux Machine:
> > #408606Linux since June 2005
> > On Thu, May 7, 2020 at 4:46 PM James <[hidden email]> wrote:
> > > On 2020-05-07 4:17 p.m., Malcolm Jaros wrote:
> > > > In Cell C1 put =IF(B1="MB",A1*1000,A1)
> > > How can I have 2 IFs in case there are GBs?
> > >
> > > --To unsubscribe e-mail to:
> > > [hidden email]
> > > Problems?
> > > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> > > Posting guidelines + more:
> > > https://wiki.documentfoundation.org/Netiquette
> > > List archive: https://listarchives.libreoffice.org/global/users/
> > > Privacy Policy: https://www.documentfoundation.org/privacy

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
bjlockie bjlockie
Reply | Threaded
Open this post in threaded view
|

Re: sum text column of common unit of measure numbers

In reply to this post by remygauthier
On 2020-05-07 6:04 p.m., Remy Gauthier wrote:

> Hi,
> If your values takes up, for instance the range A2 to A29, you can
> enter this formula as an array formula:
> =SUM(VALUE(SUBSTITUTE(SUBSTITUTE(A2:A29," KB","")," MB","")))
> The only trick is to hit CTRL-SHIFT-ENTER instead of just ENTER at the
> end of the formula entry: if you only use ENTER, this will be a
> "normal" formula and it will not work. To make sure you did it right,
> the formula should appear with curly braces on each side in the formula
> display, like this:
> {=SUM(VALUE(SUBSTITUTE(SUBSTITUTE(A2:A29," KB","")," MB","")))}
> Another thing toi be careful about: if your decimal separator is the
> comma (","), the dot in your list of numbers will also have to be
> changed. You can change the formula like this to make the change of
> separator:
> =SUM(VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2:A29," KB",""),"
> MB",""),".",",")))
> Now, if you really want to be fancy, you can use the REGEX function to
> remove the units. This will remove KB, MB, and GB:
> =SUM(VALUE(REGEX(A2:A29," [KMG]B","")))
> And don't forget that CTRL-SHIFT !
> I hope this helps.
> Rémy.
This looks promising. :-)


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Jim Byrnes Jim Byrnes
Reply | Threaded
Open this post in threaded view
|

Test

For some time now I have been unable to post to this list using gmane,
so I decided to try subscribing to the list and using email. Hopefully
this gets posted.

Regards,  Jim

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
bjlockie bjlockie
Reply | Threaded
Open this post in threaded view
|

Re: Test

On 2020-05-09 9:44 p.m., Jim wrote:
> For some time now I have been unable to post to this list using gmane,
> so I decided to try subscribing to the list and using email. Hopefully
> this gets posted.
>
> Regards,  Jim
>
https://en.wikipedia.org/wiki/Gmane
Use gmane.io


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Jim Byrnes Jim Byrnes
Reply | Threaded
Open this post in threaded view
|

Re: Test

On 5/9/20 9:09 PM, James wrote:

> On 2020-05-09 9:44 p.m., Jim wrote:
>> For some time now I have been unable to post to this list using gmane,
>> so I decided to try subscribing to the list and using email. Hopefully
>> this gets posted.
>>
>> Regards,  Jim
>>
> https://en.wikipedia.org/wiki/Gmane
> Use gmane.io
>
>

I am using gmane.io. I switched to it when the original gmane stopped
working. The problem started on the original gmane and continued to
gmane.io. I have always been able to read the group but at some point in
  time I could no longer post to it.

At some point I corresponded with a moderator. He replied to my problem
with some questions, I answered him but never heard anything back. At
that point I just gave up.

I am sending this reply via gmane.io, we'll see what happens.

Regards,  Jim


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
bjlockie bjlockie
Reply | Threaded
Open this post in threaded view
|

Re: Test

On 2020-05-10 12:22 p.m., Jim wrote:

>
> I am using gmane.io. I switched to it when the original gmane stopped
> working. The problem started on the original gmane and continued to
> gmane.io. I have always been able to read the group but at some point
> in  time I could no longer post to it.
>
> At some point I corresponded with a moderator. He replied to my
> problem with some questions, I answered him but never heard anything
> back. At that point I just gave up.
>
> I am sending this reply via gmane.io, we'll see what happens.
Maybe you need to be subscribed.
I can't find a way to check my subscription.
You could leave yourself subscribed the "-nomail" way.
> Some special-case subscribers might want to be able to post to a list
> but might not want to receive messages from that list (community
> contributors who post announcements, for instance). In this case,
> please subscribe to the so-called "nomail" version of the list by
> attaching -nomail to the normal subscription address. For example, to
> do this for The Document Foundation general discussions list, the
> subscription address would be:
> [hidden email]
> <mailto:[hidden email]>.



--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy