Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old November 29th 04, 04:00 AM
Rob van Gelder
 
Posts: n/a
Default

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   Report Post  
Old November 29th 04, 04:32 PM
Peter T
 
Posts: n/a
Default

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   Report Post  
Old November 29th 04, 09:03 PM
Peter T
 
Posts: n/a
Default

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






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formating h:mm AM/PM Mike R Excel Discussion (Misc queries) 3 January 7th 05 02:12 AM
Formating, paragraph marks, boxes etc, how do I get rid of them? Josh Excel Discussion (Misc queries) 1 December 27th 04 08:33 PM
Formating stevieray Excel Discussion (Misc queries) 1 November 30th 04 11:30 PM
Formating output. JustSomeGuy Excel Discussion (Misc queries) 12 November 29th 04 09:03 PM
30 Day Aging Report Using Conditional Formating Tiegris Excel Worksheet Functions 0 November 4th 04 10:07 PM


All times are GMT +1. The time now is 01:34 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017