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

"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.