Custom Format : Gb/Mb/kb/bytes : Need help debugging command

[Asc3nti0n]

Hi Everyone,

I'm trying to format a few series of bytes as something that is a
little more legible, and truncating the number of decimal points.

While the link 'Local Tips on Custom Formatting eg.'
(http://www.exceltip.com/st/Formatting_numbers_according_to_criteria_in_Microsoft_Excel/71.html)
has been a great aid in getting the general drift of how to write the
custom format strings, it doesn't aid debugging something that errors.

I have the following line:

[<1099511627776]((#,##0.000)/1099511627776)"
TB";[<1073741824]((#,##0.000)/1073741824)"
GB";[<1048576]((#,##0.000)/1048576)" MB";[<1024]((#,##0.000)/1024)"
kB";(#,##0)" Bytes"

I have been able to add the byte suffix to the amount, and divide the
source number of bytes with a nested IF statement:

=CONCATENATE((IF($P2>1024,(IF($P2>(1024*1024),(IF($P2>(1024*1024*1024),$P2/(1024*1024*1024),$P2/(1024*1024))),$P2/(1024))),$P2)),"
",(IF($P2>1024,(IF($P2>(1024*1024),(IF($P2>(1024*1024*1024),"GB","MB")),"kB")),"Bytes")))

Where P is the column I have copied the raw byte amount across to,
outside the printable space.

It's logically correct, but this method doesn't allow for the reduction
of the decimal points. Because CONC output appears as a text field to
excel.

So if the Custom Format string could be made to reflect it, and the
0.000 function of it work correctly....

Any comments appreciated.

Thanks
Tim Leech
IT User Committee Coordinator, Accom. Providers, La Trobe
University, Melbourne, AUSTRALIA.

 

[Ron Rosenfeld]

Hi Everyone,

I'm trying to format a few series of bytes as something that is a
little more legible, and truncating the number of decimal points.

While the link 'Local Tips on Custom Formatting eg.'
(http://www.exceltip.com/st/Formatting_numbers_according_to_criteria_in_Microsoft_Excel/71.html)
has been a great aid in getting the general drift of how to write the
custom format strings, it doesn't aid debugging something that errors.

I have the following line:

[<1099511627776]((#,##0.000)/1099511627776)"
TB";[<1073741824]((#,##0.000)/1073741824)"
GB";[<1048576]((#,##0.000)/1048576)" MB";[<1024]((#,##0.000)/1024)"
kB";(#,##0)" Bytes"

I have been able to add the byte suffix to the amount, and divide the
source number of bytes with a nested IF statement:

=CONCATENATE((IF($P2>1024,(IF($P2>(1024*1024),(IF($P2>(1024*1024*1024),$P2/(1024*1024*1024),$P2/(1024*1024))),$P2/(1024))),$P2)),"
",(IF($P2>1024,(IF($P2>(1024*1024),(IF($P2>(1024*1024*1024),"GB","MB")),"kB")),"Bytes")))

Where P is the column I have copied the raw byte amount across to,
outside the printable space.

It's logically correct, but this method doesn't allow for the reduction
of the decimal points. Because CONC output appears as a text field to
excel.

So if the Custom Format string could be made to reflect it, and the
0.000 function of it work correctly....

Any comments appreciated.

Thanks
Tim Leech
IT User Committee Coordinator, Accom. Providers, La Trobe
University, Melbourne, AUSTRALIA.

Tim,

So far as I know, you cannot include the process of division in custom
formatting. I would either use a macro to format the entry in place (and do
the division), or a UDF that would put the result in another column.

One could also write an event driven macro that would automatically format (and
divide) entries made in certain cells.

Here's an idea to use as a macro, and it could be easily adapted to the other
forms I mentioned above, depending on your requirements.

===========================
Sub FormatByte()
Dim c As Range
Dim n As Double

For Each c In Selection
Select Case c.Value
Case Is < 1024
c.NumberFormat = "0.000 \B"
Case Is < 1024 ^ 2
c.NumberFormat = "0.000 \k\B"
c.Value = c.Value / 1024
Case Is < 1024 ^ 3
c.NumberFormat = "0.000 \M\B"
c.Value = c.Value / 1024 ^ 2
Case Else
c.NumberFormat = "0.000 \G\B"
c.Value = c.Value / 1024 ^ 3
End Select
Next c

End Sub
==================


--ron

 

[J.E. McGimpsey]

XL's number formats can only have a maximum of 4 fields, the last of
which is reserved for Text. Format also won't do math, so it won't
convert, for example, 2048 to 2 KB.

One way to us a formula:

=$P2/1024^(INT(LOG($P2,1024))) & " " & CHOOSE(INT(LOG($P2,1024))+1,
"bytes","KB","MB","GB","TB","PB","EB")

Hi Everyone,

I'm trying to format a few series of bytes as something that is a
little more legible, and truncating the number of decimal points.

While the link 'Local Tips on Custom Formatting eg.'
(http://www.exceltip.com/st/Formatting_numbers_according_to_criteria_in_Micros
oft_Excel/71.html)
has been a great aid in getting the general drift of how to write the
custom format strings, it doesn't aid debugging something that errors.

I have the following line:

[<1099511627776]((#,##0.000)/1099511627776)"
TB";[<1073741824]((#,##0.000)/1073741824)"
GB";[<1048576]((#,##0.000)/1048576)" MB";[<1024]((#,##0.000)/1024)"
kB";(#,##0)" Bytes"

I have been able to add the byte suffix to the amount, and divide the
source number of bytes with a nested IF statement:

=CONCATENATE((IF($P2>1024,(IF($P2>(1024*1024),(IF($P2>(1024*1024*1024),$P2/(
1024*1024*1024),$P2/(1024*1024))),$P2/(1024))),$P2)),"
",(IF($P2>1024,(IF($P2>(1024*1024),(IF($P2>(1024*1024*1024),"GB","MB")),"kB"
)),"Bytes")))

Where P is the column I have copied the raw byte amount across to,
outside the printable space.

It's logically correct, but this method doesn't allow for the reduction
of the decimal points. Because CONC output appears as a text field to
excel.

So if the Custom Format string could be made to reflect it, and the
0.000 function of it work correctly....

Any comments appreciated.

Thanks
Tim Leech
IT User Committee Coordinator, Accom. Providers, La Trobe
University, Melbourne, AUSTRALIA.

 

[Peo Sjoblom]

Nice one..

--

Regards,

Peo Sjoblom

XL's number formats can only have a maximum of 4 fields, the last of
which is reserved for Text. Format also won't do math, so it won't
convert, for example, 2048 to 2 KB.

One way to us a formula:

=$P2/1024^(INT(LOG($P2,1024))) & " " & CHOOSE(INT(LOG($P2,1024))+1,
"bytes","KB","MB","GB","TB","PB","EB")

Hi Everyone,

I'm trying to format a few series of bytes as something that is a
little more legible, and truncating the number of decimal points.

While the link 'Local Tips on Custom Formatting eg.'

(http://www.exceltip.com/st/Formatting_numbers_according_to_criteria_in_Micr
os

oft_Excel/71.html)
has been a great aid in getting the general drift of how to write the
custom format strings, it doesn't aid debugging something that errors.

I have the following line:

[<1099511627776]((#,##0.000)/1099511627776)"
TB";[<1073741824]((#,##0.000)/1073741824)"
GB";[<1048576]((#,##0.000)/1048576)" MB";[<1024]((#,##0.000)/1024)"
kB";(#,##0)" Bytes"

I have been able to add the byte suffix to the amount, and divide the
source number of bytes with a nested IF statement:

=CONCATENATE((IF($P2>1024,(IF($P2>(1024*1024),(IF($P2>(1024*1024*1024),$P2/(
1024*1024*1024),$P2/(1024*1024))),$P2/(1024))),$P2)),"
",(IF($P2>1024,(IF($P2>(1024*1024),(IF($P2>(1024*1024*1024),"GB","MB")),"kB"
)),"Bytes")))

Where P is the column I have copied the raw byte amount across to,
outside the printable space.

It's logically correct, but this method doesn't allow for the reduction
of the decimal points. Because CONC output appears as a text field to
excel.

So if the Custom Format string could be made to reflect it, and the
0.000 function of it work correctly....

Any comments appreciated.

Thanks
Tim Leech
IT User Committee Coordinator, Accom. Providers, La Trobe
University, Melbourne, AUSTRALIA.

 

[J.E. McGimpsey]

Nice one..

Thanks!