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" <peter_t@discussions 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" <peter_t@discussions 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