Remember Me?

Menu

#11
November 29th 04, 05:00 AM
 Rob van Gelder Posts: n/a

That still wont work.

Doing the ,,,, thing effectively divides by 1000 again and again.
This is not the same as dividing by 1024.

The ,,, way:
12345678901234 bytes
12345678901 kilobytes
12345679 megabytes
12346 gigabytes
12 terabytes

The real way:
12345678901234 bytes
12056327052 kilobytes
11773757 megabytes
11498 gigabytes
11 terabytes

Using a secondary column containing a formula for presentation is probably
the only accurate option.

--
Rob van Gelder - http://www.vangelder.co.nz/excel

"Peter T" <[email protected] wrote in message
...

"Rob van Gelder" wrote in message
...
Pretty neat stuff with the ,,, business.

Nothing like a good comma here and there...

Just to add a layer of complexity...
There is 1024 bytes to a kilobyte
1024 kilobytes to a megabyte
1024 megabytes to a gigabyte

Oh dear!
Another one for Brian to play with:

Sub FileFormat2()
Dim sCustom As String
For Each r In Selection
Select Case r.Value
Case Is = 2 ^ 40: sCustom = "#,,,,"" Tb"""
Case Is = 2 ^ 39: sCustom = """.5 Tb"""
Case Is = 2 ^ 30: sCustom = "#,,,"" Gb"""
Case Is = 2 ^ 29: sCustom = """.5 Gb"""
Case Is = 2 ^ 20: sCustom = "#,,"" Mb"""
Case Is = 2 ^ 19: sCustom = """.5 Mb"""
Case Is = 2 ^ 10: sCustom = "#,"" Kb"""
Case Is = 2 ^ 9: sCustom = """0.5 Kb"""
Case Else: sCustom = """ <0.5 Kb"""
End Select
r.NumberFormat = sCustom
Next
'for quarters use 2^8, 2^18 etc & add to 2^9 etc for 3/4
End Sub

Brian, will need to run custom code each time any of your numbers change.
As
Ron mentioned a macro triggered by an appropriate "Event" is convenient to
make it run automatically when required, and on the correct range of
cells.
You might have a look at Chip Pearson's introduction:
http://www.cpearson.com/excel/events.htm

If you don't need to format your values and can accept to display in a
separate list, maybe something like this:

Sub FileFormat3()
Dim r As Range, rng As Range
Dim n As Single, s As String, v

v = Array(" B", " Kb", " Mb", " Gb", " Tb")
Set rng = Selection

For Each r In rng
For i = 40 To 10 Step -10
If r = 2 ^ i Then
Exit For
End If
Next
n = r / (2 ^ i)
If i Then s = "##,##0.0" Else s = "##,##0 "
s = Format(n, s & v(i / 10))
r.Offset(0, 1) = s 'place in adjacent cell

'r.NumberFormat = Chr(34) & s & Chr(34)
'Theoretically possible, but quickly builds up unwanted custom formats
'unless deleted at top of routine. Also there's a limit to total custom
formats.
Next

End Sub

Probably best to discard a workbook used for testing custom number
formats,
or delete all the unused ones.

Regards,
Peter

--
Rob van Gelder - http://www.vangelder.co.nz/excel

"Peter T" <[email protected] wrote in message
...
Brian,

I think you should be able to format as you wish and retain values for
summing.

For starters, but not quite what you want:

Sub FileFormat()
Dim sCustom As String, r as Range

For Each r In Selection
Select Case r.Value
Case Is = 10 ^ 9: sCustom = "#.###,,,"" Gb"""
Case Is = 10 ^ 6: sCustom = "#.###,,"" Mb"""
Case Is = 10 ^ 3: sCustom = "#.###,"" Kb"""
Case Else: sCustom = "###"" B"""
End Select
r.NumberFormat = sCustom
Next

End Sub

I'll leave you to adapt to your needs, say:
change the case values to adapt to your .5 & .75 etc
reduce the "###", or
remove the ".",
replace ### with ".5Kb" or whatever.
maybe change alignment a little with some spaces in the string
Add more case's to suit.

When done maybe you can put an event routine.

Regards,
Peter

"JustSomeGuy" wrote in message
...
I have a number which is the size of a file on disk.
Depending on the size of the number I want to display it as either
1.5KB, or 2.0MB or .75GB or .5TB

However I want to be able to add up all the numbers in that column as
normal
numbers and also format it the same way.

How do I do this.... I do know VB very well...

Brian.

#12
November 29th 04, 05:32 PM
 Peter T Posts: n/a

Hi Rob,

I should have quit whilst ahead. Thanks to your observations my FileFormat2
has gone direct to the bin without passing Recycle!

Think my string function FileFormat3 was OK though,
12345678901234 / 1024^4 = 11.2 Tb

Regards,
Peter

"Rob van Gelder" wrote in message
...
That still wont work.

Doing the ,,,, thing effectively divides by 1000 again and again.
This is not the same as dividing by 1024.

The ,,, way:
12345678901234 bytes
12345678901 kilobytes
12345679 megabytes
12346 gigabytes
12 terabytes

The real way:
12345678901234 bytes
12056327052 kilobytes
11773757 megabytes
11498 gigabytes
11 terabytes

Using a secondary column containing a formula for presentation is probably
the only accurate option.

--
Rob van Gelder - http://www.vangelder.co.nz/excel

"Peter T" <[email protected] wrote in message
...

"Rob van Gelder" wrote in

message
...
Pretty neat stuff with the ,,, business.

Nothing like a good comma here and there...

Just to add a layer of complexity...
There is 1024 bytes to a kilobyte
1024 kilobytes to a megabyte
1024 megabytes to a gigabyte

Oh dear!
Another one for Brian to play with:

Sub FileFormat2()
Dim sCustom As String
For Each r In Selection
Select Case r.Value
Case Is = 2 ^ 40: sCustom = "#,,,,"" Tb"""
Case Is = 2 ^ 39: sCustom = """.5 Tb"""
Case Is = 2 ^ 30: sCustom = "#,,,"" Gb"""
Case Is = 2 ^ 29: sCustom = """.5 Gb"""
Case Is = 2 ^ 20: sCustom = "#,,"" Mb"""
Case Is = 2 ^ 19: sCustom = """.5 Mb"""
Case Is = 2 ^ 10: sCustom = "#,"" Kb"""
Case Is = 2 ^ 9: sCustom = """0.5 Kb"""
Case Else: sCustom = """ <0.5 Kb"""
End Select
r.NumberFormat = sCustom
Next
'for quarters use 2^8, 2^18 etc & add to 2^9 etc for 3/4
End Sub

Brian, will need to run custom code each time any of your numbers

change.
As
Ron mentioned a macro triggered by an appropriate "Event" is convenient

to
make it run automatically when required, and on the correct range of
cells.
You might have a look at Chip Pearson's introduction:
http://www.cpearson.com/excel/events.htm

If you don't need to format your values and can accept to display in a
separate list, maybe something like this:

Sub FileFormat3()
Dim r As Range, rng As Range
Dim n As Single, s As String, v

v = Array(" B", " Kb", " Mb", " Gb", " Tb")
Set rng = Selection

For Each r In rng
For i = 40 To 10 Step -10
If r = 2 ^ i Then
Exit For
End If
Next
n = r / (2 ^ i)
If i Then s = "##,##0.0" Else s = "##,##0 "
s = Format(n, s & v(i / 10))
r.Offset(0, 1) = s 'place in adjacent cell

'r.NumberFormat = Chr(34) & s & Chr(34)
'Theoretically possible, but quickly builds up unwanted custom

formats
'unless deleted at top of routine. Also there's a limit to total

custom
formats.
Next

End Sub

Probably best to discard a workbook used for testing custom number
formats,
or delete all the unused ones.

Regards,
Peter

--
Rob van Gelder - http://www.vangelder.co.nz/excel

"Peter T" <[email protected] wrote in message
...
Brian,

I think you should be able to format as you wish and retain values

for
summing.

For starters, but not quite what you want:

Sub FileFormat()
Dim sCustom As String, r as Range

For Each r In Selection
Select Case r.Value
Case Is = 10 ^ 9: sCustom = "#.###,,,"" Gb"""
Case Is = 10 ^ 6: sCustom = "#.###,,"" Mb"""
Case Is = 10 ^ 3: sCustom = "#.###,"" Kb"""
Case Else: sCustom = "###"" B"""
End Select
r.NumberFormat = sCustom
Next

End Sub

I'll leave you to adapt to your needs, say:
change the case values to adapt to your .5 & .75 etc
reduce the "###", or
remove the ".",
replace ### with ".5Kb" or whatever.
maybe change alignment a little with some spaces in the string
Add more case's to suit.

When done maybe you can put an event routine.

Regards,
Peter

#13
November 29th 04, 10:03 PM
 Peter T Posts: n/a

Hi Rob,

I should have quit whilst ahead. Thanks to your observations my FileFormat2
has gone direct to the bin without passing Recycle!

Think my string function FileFormat3 was OK though,
12345678901234 / 1024^4 = 11.2 Tb

As you say, probably best as a cell formula for display purposes, perhaps
along similar lines.

Regards,
Peter

"Rob van Gelder" wrote in message
...
That still wont work.

Doing the ,,,, thing effectively divides by 1000 again and again.
This is not the same as dividing by 1024.

The ,,, way:
12345678901234 bytes
12345678901 kilobytes
12345679 megabytes
12346 gigabytes
12 terabytes

The real way:
12345678901234 bytes
12056327052 kilobytes
11773757 megabytes
11498 gigabytes
11 terabytes

Using a secondary column containing a formula for presentation is probably
the only accurate option.

--
Rob van Gelder - http://www.vangelder.co.nz/excel

"Peter T" <[email protected] wrote in message
...

"Rob van Gelder" wrote in

message
...
Pretty neat stuff with the ,,, business.

Nothing like a good comma here and there...

Just to add a layer of complexity...
There is 1024 bytes to a kilobyte
1024 kilobytes to a megabyte
1024 megabytes to a gigabyte

Oh dear!
Another one for Brian to play with:

Sub FileFormat2()
Dim sCustom As String
For Each r In Selection
Select Case r.Value
Case Is = 2 ^ 40: sCustom = "#,,,,"" Tb"""
Case Is = 2 ^ 39: sCustom = """.5 Tb"""
Case Is = 2 ^ 30: sCustom = "#,,,"" Gb"""
Case Is = 2 ^ 29: sCustom = """.5 Gb"""
Case Is = 2 ^ 20: sCustom = "#,,"" Mb"""
Case Is = 2 ^ 19: sCustom = """.5 Mb"""
Case Is = 2 ^ 10: sCustom = "#,"" Kb"""
Case Is = 2 ^ 9: sCustom = """0.5 Kb"""
Case Else: sCustom = """ <0.5 Kb"""
End Select
r.NumberFormat = sCustom
Next
'for quarters use 2^8, 2^18 etc & add to 2^9 etc for 3/4
End Sub

Brian, will need to run custom code each time any of your numbers

change.
As
Ron mentioned a macro triggered by an appropriate "Event" is convenient

to
make it run automatically when required, and on the correct range of
cells.
You might have a look at Chip Pearson's introduction:
http://www.cpearson.com/excel/events.htm

If you don't need to format your values and can accept to display in a
separate list, maybe something like this:

Sub FileFormat3()
Dim r As Range, rng As Range
Dim n As Single, s As String, v

v = Array(" B", " Kb", " Mb", " Gb", " Tb")
Set rng = Selection

For Each r In rng
For i = 40 To 10 Step -10
If r = 2 ^ i Then
Exit For
End If
Next
n = r / (2 ^ i)
If i Then s = "##,##0.0" Else s = "##,##0 "
s = Format(n, s & v(i / 10))
r.Offset(0, 1) = s 'place in adjacent cell

'r.NumberFormat = Chr(34) & s & Chr(34)
'Theoretically possible, but quickly builds up unwanted custom

formats
'unless deleted at top of routine. Also there's a limit to total

custom
formats.
Next

End Sub

Probably best to discard a workbook used for testing custom number
formats,
or delete all the unused ones.

Regards,
Peter

--
Rob van Gelder - http://www.vangelder.co.nz/excel

"Peter T" <[email protected] wrote in message
...
Brian,

I think you should be able to format as you wish and retain values

for
summing.

For starters, but not quite what you want:

Sub FileFormat()
Dim sCustom As String, r as Range

For Each r In Selection
Select Case r.Value
Case Is = 10 ^ 9: sCustom = "#.###,,,"" Gb"""
Case Is = 10 ^ 6: sCustom = "#.###,,"" Mb"""
Case Is = 10 ^ 3: sCustom = "#.###,"" Kb"""
Case Else: sCustom = "###"" B"""
End Select
r.NumberFormat = sCustom
Next

End Sub

I'll leave you to adapt to your needs, say:
change the case values to adapt to your .5 & .75 etc
reduce the "###", or
remove the ".",
replace ### with ".5Kb" or whatever.
maybe change alignment a little with some spaces in the string
Add more case's to suit.

When done maybe you can put an event routine.

Regards,
Peter

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Mike R Excel Discussion (Misc queries) 3 January 7th 05 03:12 AM Josh Excel Discussion (Misc queries) 1 December 27th 04 09:33 PM stevieray Excel Discussion (Misc queries) 1 December 1st 04 12:30 AM JustSomeGuy Excel Discussion (Misc queries) 12 November 29th 04 10:03 PM Tiegris Excel Worksheet Functions 0 November 4th 04 11:07 PM

All times are GMT +1. The time now is 04:52 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright ©2004-2021 ExcelBanter.
The comments are property of their posters.

About Us

"It's about Microsoft Excel"

Copyright © 2017