Re: [libreoffice-users] 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*100)) > > -- Registered Linux User: #480675Registered Linux Machine: > #408606Linux since June 2005 > On Thu, May 7, 2020 at 4:56 PM Wade Smart > 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 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: > > > users+unsubscr...@global.libreoffice.org > > > 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: users+unsubscr...@global.libreoffice.org 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
[libreoffice-users] Re: sum text column of common unit of measure numbers
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: users+unsubscr...@global.libreoffice.org
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
Re: [libreoffice-users] Re: sum text column of common unit of measure numbers
=IF(B1="kb",A3, IF(B1="mb",A1*1000,A1*100)) -- Registered Linux User: #480675 Registered Linux Machine: #408606 Linux since June 2005 On Thu, May 7, 2020 at 4:56 PM Wade Smart 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 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: users+unsubscr...@global.libreoffice.org > > 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: users+unsubscr...@global.libreoffice.org 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
Re: [libreoffice-users] 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 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: users+unsubscr...@global.libreoffice.org > 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: users+unsubscr...@global.libreoffice.org 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
[libreoffice-users] Re: sum text column of common unit of measure numbers
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: users+unsubscr...@global.libreoffice.org 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
