Formating output.

J

JustSomeGuy

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

Rob van Gelder

Suggest you use another column for presentation and keep the original column
for subtotals.
 
A

Arvi Laanemets

Hi

I think it's impossible to format the cell in such a way, but formula below
calculates entered value in desired format in additional column (the result
will be a text string of course - in any calculations you have to use data
in original column). For number in cell A1 the formula will be

=A1/CHOOSE(MATCH(A1;{0;500;500000;500000000;500000000000};1);1;1000;1000000;
1000000000;1000000000000) & " " &
CHOOSE(MATCH(A1;{0;500;500000;500000000;500000000000};1);"";"k";"M";"G";"T")
& "B"

Numbers in array part (figural brackets) of MATCH function determine levels,
where scale will change. In my formula it's 0.5 of next-level measuring
unit - you can change those numbers according your needs.

Probably you have to replace semicolons in formula with commas too.


Arvi Laanemets
 
P

Peter T

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
 
R

Ron Rosenfeld

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.
Click to expand...

Well, since you know VB well, that would be one way to do it. You could use an
event macro, and format the cell depending on the contents.

With this method, though, you cannot get fractions of a nB.

For example:

================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim aoi As Range
Dim c As Range
Set aoi = [a1:a100]

For Each c In aoi
Select Case c.Value
Case Is >= 10 ^ 12
c.NumberFormat = "0,,,,.00 \T\B"
Case Is >= 10 ^ 9
c.NumberFormat = "0,,,.00\G\B"
Case Is >= 10 ^ 6
c.NumberFormat = "0,,.00\M\B"
Case Is >= 10 ^ 3
c.NumberFormat = "0,.00\K\B"
Case Else
c.NumberFormat = "General"
End Select
Next c

End Sub
=========================
--ron
 
R

Rob van Gelder

Pretty neat stuff with the ,,, business.

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

Though 1000 may well be close enough for the OP.
 
R

Ron Rosenfeld

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.
Click to expand...

Well, since you know VB well, that would be one way to do it. You could use an
event macro, and format the cell depending on the contents.

With this method, though, you cannot get fractions of a nB.

For example:

================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim aoi As Range
Dim c As Range
Set aoi = [a1:a100]

For Each c In aoi
Select Case c.Value
Case Is >= 10 ^ 12
c.NumberFormat = "0,,,,.00 \T\B"
Case Is >= 10 ^ 9
c.NumberFormat = "0,,,.00\G\B"
Case Is >= 10 ^ 6
c.NumberFormat = "0,,.00\M\B"
Case Is >= 10 ^ 3
c.NumberFormat = "0,.00\K\B"
Case Else
c.NumberFormat = "General"
End Select
Next c

End Sub
=========================
--ron
Click to expand...

Sorry, I was wrong about fractions of an nB. Use this routine:

=====================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim aoi As Range
Dim c As Range
Set aoi = [a1:a100]

For Each c In aoi
Select Case c.Value
Case Is >= 0.5 * 10 ^ 12
c.NumberFormat = "0.00,,,, \T\B"
Case Is >= 0.5 * 10 ^ 9
c.NumberFormat = "0.00,,,\G\B"
Case Is >= 0.5 * 10 ^ 6
c.NumberFormat = "0.00,,\M\B"
Case Is >= 0.5 * 10 ^ 3
c.NumberFormat = "0.00,\K\B"
Case Else
c.NumberFormat = "General"
End Select
Next c

End Sub

======================

--ron
 
S

ShipHead

Many thanks all!
I will try the vb approach.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim aoi As Range
Dim c As Range
Set aoi = [a1:a100]

For Each c In aoi
Select Case c.Value
Case Is >= 0.5 * 10 ^ 12
c.NumberFormat = "0.00,,,, \T\B"
Case Is >= 0.5 * 10 ^ 9
c.NumberFormat = "0.00,,,\G\B"
Case Is >= 0.5 * 10 ^ 6
c.NumberFormat = "0.00,,\M\B"
Case Is >= 0.5 * 10 ^ 3
c.NumberFormat = "0.00,\K\B"
Case Else
c.NumberFormat = "General"
End Select
Next c

End Sub

A question here ... Why did you choose a1:a100 is this just a hypothetical
location? I guess I was thinking that I could integrate my own 'function'
in VB into excel... Something like Date, which keeps the underlying value
but modifies the display properties.

If I understand your example then when excel is ready to redisplay the
worksheet
your Worksheet_Change function gets called. But I assume it only gets
called
for the ragne you specify. (I know VB well I'm just not used to using it in
excel,
if you know what I mean.)



////////////////////////////////////////////////////////////////////////////
// At the moment my routine is in c++ and looks like this:
// I will translate this to its VB equivalent...
////////////////////////////////////////////////////////////////////////////
double KMGT::intpart(double dv)
{
double di(0);
modf(dv, &di);
return(di);
}
ostream & operator<<(ostream & o, const KMGT & n_)
{
using namespace std;
double k, m, g, t;

k = n_.n / 1024.;
m = n_.n / 1048576.;
g = n_.n / 1073741824.;
t = n_.n / 1099511627776.;

int p = o.precision();
ios_base::fmtflags f = o.flags();

o << setprecision(1) << fixed;
if (n_.intpart(t) != 0)
o << t << "TB";
else if (n_.intpart(g) != 0)
o << g << "GB";
else if (n_.intpart(m) != 0)
o << m << "MB";
else if (n_.intpart(k) != 0)
o << k << "KB";
else
o << n_.n << "B";
o.precision( p );
o.flags( f );
return(o);
}
 
R

Ron Rosenfeld

A question here ... Why did you choose a1:a100 is this just a hypothetical
location?
Click to expand...

That is just a hypothetical location. You should change it to the range in
which these numbers will be entered.
I guess I was thinking that I could integrate my own 'function'
in VB into excel... Something like Date, which keeps the underlying value
but modifies the display properties.
Click to expand...

In Excel, functions cannot modify cell properties (including format). As far
as I know, the DATE worksheet function in Excel does not "modify" the display
properties (if by that you mean the cell format).

But perhaps I am misunderstanding you. Could you give an example as to exactly
what you mean?
If I understand your example then when excel is ready to redisplay the
worksheet
your Worksheet_Change function gets called. But I assume it only gets
called
for the ragne you specify. (I know VB well I'm just not used to using it in
excel,
if you know what I mean.)
Click to expand...

The worksheet change event is generated whenever the worksheet changes. Look
at VBA HELP for Change Event. This macro I wrote runs whenever the change
event occurs, but it only checks the range defined.

You can certainly rewrite it to check other cells, to evaluate the contents in
some way and apply formatting based on additional rules, etc.

For speed purposes, you want to check as few cells as possible. If you set aoi
= [A;A] for example, you will see what I mean about speed.


--ron
 
P

Peter T

Rob van Gelder said:
Pretty neat stuff with the ,,, business.
Click to expand...

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
Click to expand...

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
 
R

Rob van Gelder

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

Peter T

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
 
P

Peter T

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
 
Top