Format a cell with a custom number format

I would like to type in a number and if the number meets a certain criteria,
return that number with a unit identifier.

I.E. a number "A1<= 9.9" returns "value for A1GHz" and if "A1>=100, A1<=999"
returns "value for A1MHz"

I would to type a single place, decimal digit under 10 and get that value
with the unit identifier "GHz". By the same token, if the number is greater
100, but less than 999, show the value with the identifier "MHz".

Thank You

Armor,

This may not be the sort of thing you're looking for, but I think it works.
Hopefully there's a simpler solution that someone else can offer -- but I
don't know what it might be.

You've got to write a macro, or actually just paste this one into your
worksheet.

First the macro:
--------------------------------------------------
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Static Skip As Boolean
If Not Skip Then
'If Skip is true, the routine will not run.
'This is used to prevent the routine from causing
'itself to run.
Skip = False

'Check for valid conditions
If Target.Rows.Count > 1 Then Exit Sub
If Target.Columns.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
If Target.Row > 5 Or Target.Row < 2 Then Exit Sub
If Target.Column <> 2 Then Exit Sub
If Not IsNumeric(Target) Then Exit Sub

If Target <> Round(Target, 1) Then
MsgBox ("You have too many digits after the decimal")
Exit Sub
End If

'Do the actual conversion
Skip = True
If Target <= 9.9 And Target > 0 Then
Target = CStr(Target) & "GHz"
ElseIf Target >= 100 And Target <= 999 Then
Target = CStr(Target) & "MHz"
Else
'A final invalid condition with regard to the range.
MsgBox (Target & " is an invalid value")
End If
End If
Skip = False
End Sub
--------------------------------------------------

Now, what do you do with it.

Open the Visual Basic Editor -- either through Tools, Macros or by hitting
Alt-F11.

When you do that you should see a whole bunch of stuff. On the left you'll
see a tree of workbooks, worksheets and some other stuff. Find your workbook
and worksheet.

Double Click your sheet and you should see a blank panel in the center.
Paste the macro there.

The macro will respond to any changes in values on that particular sheet
only. I've put some stuff in there so that you can isolate a range of cells
that should be affected. In my example you can enter you values only in rows
2-5 and column B.

Good luck. I won't be offended if this is not the sort of thing that you
want to use.

Art

"Armor" wrote:

> I would like to type in a number and if the number meets a certain criteria,
> return that number with a unit identifier.
>
> I.E. a number "A1<= 9.9" returns "value for A1GHz" and if "A1>=100, A1<=999"
> returns "value for A1MHz"
>
> I would to type a single place, decimal digit under 10 and get that value
> with the unit identifier "GHz". By the same token, if the number is greater
> 100, but less than 999, show the value with the identifier "MHz".
>
> Thank You

Something like this: [<10]0.0" GHz";0" MHz" will display any number <10 as "x.x GHz" and anything >10 will be displayed as "xxx MHz"

You didn't say what to do with values between 10 and 100, so I left those to be formatted as "xx MHz" but you could add another condition to do something different with those values.

This works for a limited number of categories (up to three or four, I think). Play around with it and see if it helps.

Wow -- I was hoping someone knew an easy way to this. I didn't know that you
could put those sorts of conditions in a format!

Art

"MrShorty" wrote:

>
> Something like this: [<10]0.0" GHz";0" MHz" will display any number <10
> as "x.x GHz" and anything >10 will be displayed as "xxx MHz"
>
> You didn't say what to do with values between 10 and 100, so I left
> those to be formatted as "xx MHz" but you could add another condition
> to do something different with those values.
>
> This works for a limited number of categories (up to three or four, I
> think). Play around with it and see if it helps.
>
>
> --
> MrShorty
> ------------------------------------------------------------------------
> MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
> View this thread: http://www.excelforum.com/showthread...hreadid=505421
>
>

Both of you are steely-eyed Excel Heroes.
To answer the question about from Mr. Shorty --"You didn't say what to do
with values between 10 and 100, so I left those to be formatted as "xx MHz"
but you could add another condition to do something different with those
values." --
Most of the systems I catalog, the hardware is 450MHz to 1200Mhz multi-CPU
systems. Commonplace numbers being 450Mhz. The multi-CPU 1200MHz are
reported as 1.2GHz systems (hand-rounding); hence, the reason for the chasm
of 10 to 100.
I could have actually said 450, but get nipped when a 400MHz system could
appear at a field site. (Murphy’s Law). By the way gentleman, this will
also work when reporting MByte systems and GByte systems for "hard-storage"
and RAM capacity.
Again, my thanks.


"Art" wrote:

> Wow -- I was hoping someone knew an easy way to this. I didn't know that you
> could put those sorts of conditions in a format!
>
> Art
>
> "MrShorty" wrote:
>
> >
> > Something like this: [<10]0.0" GHz";0" MHz" will display any number <10
> > as "x.x GHz" and anything >10 will be displayed as "xxx MHz"
> >
> > You didn't say what to do with values between 10 and 100, so I left
> > those to be formatted as "xx MHz" but you could add another condition
> > to do something different with those values.
> >
> > This works for a limited number of categories (up to three or four, I
> > think). Play around with it and see if it helps.
> >
> >
> > --
> > MrShorty
> > ------------------------------------------------------------------------
> > MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
> > View this thread: http://www.excelforum.com/showthread...hreadid=505421
> >
> >