Format Cell - Excel 97 2

[ChrisBurch]

Is there any way to format a cell so that:-

[u}data entry[/u] Displays
1 .001
2 .002
. .
. .
9 .009 Chris

It worked yesterday.
It doesn't work today.
That's Windows!

 

[DaleWatson123321]

Chris,

I can't think of a "direct" type of format, but here's an alternative...

1) If column A is your "data entry" column, then format those cells with the color "white", so that the numbers are not displayed. And perhaps make the column narrow, so it doesn't occupy as much room, but you'll still have to identify it as the entry column.

2) Use column B as the "display" column. In column B, use the following formula...
=IF(OR(A1=0,A1="&quot,". ",(A1/1000))

3) Format column B - Click on the "B" to pick the entire column, then use Format - Cells - Custom, and then immediately under the heading "Type", enter ".000"

4) Also Right-Align the column B... Click on the "B", and choose the "Right-Alignment" icon. This will align the "." to the right of the cell - for those cases where column A contains a "0" or is BLANK.

Hope this alternative is useful.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[ChrisBurch]

Thanks Dale,

I changed the way I was representing the input to -000 (eg J012345-000). It's not exactly correct, but everyone knows what it means and it was much easier to handle.

Thanks, Chris

It worked yesterday.
It doesn't work today.
That's Windows!

 

[bkpchs237]

ChrisBurch,

Try Tools, Options, Edit tab, Fixed decimals, places 3.

Hope this helps.

 

[henkus]

Try Format,cells,number,custom and type .000
This seems to do the job for me .

cheers
henkus

 

[MrProgrammer]

".00"#.##0


data_entry Displays
2 .002
3 .003
4 .004
5 .005
6 .006
7 .007
8 .008
9 .009
10 .0010 (I think this solves your problem)

 

[DaleWatson123321]

Mr. Programmer,

I like your approach - that of attempting to create a "customized format".

However, in testing out your example, I found the following "discrepancies" between your example and the example data provided by "CbrisBurch"...

1) by using - ".00"#.##0 - it provides an EXTRA decimal. I expect that instead you meant to say - ".00"#

2) by using your example, you are effectively "padding" ALL entries with "00" - whereas I believe the REQUIRED result needs to be DIVIDED by 1000. Using your example, take the numbers 1 and 10 - the (numerical) result will be identical. The only difference will be the way in which the two numbers are displayed - i.e. 1 will be .001, and 10 will be .0010.

3) your example displays "0" as ".00", whereas ChrisBurch's example shows that (in the case of "0&quot, he wanted the decimal to show - in the same alignment as all the other decimals - and with blank space following the decimal (not "000" or "00&quot.

If you take a closer look at my example, you'll begin to appreciate why I went "out of bounds" in my effort to create the EXACT format ChrisBurch requested. My method, even while it was "out of the ordinary", it still WORKED PERFECTLY according to the specifications.

As I said at the beginning, I appreciate YOUR approach because it "says with the ballpark". Therefore, IF you can come up with a variation of your "customized format" which works according to the stated specifications, then you will DEFINITELY have a "WINNER".

I hope you can take this "back to your drawing board" and come up with that "winning combination". Indeed, if you can come up with a customized format solution, then it will be one which MANY Tek-Tippers will be able to learn from and utilize !!!

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[ChrisBurch]

Thanks all, I hadn't realised that this thread had kept going.

MrProgrammer

Thanks for the efforts, you've just opened my eyes a lot more to custom formats. Certainly, I don't understand them enough....which will be rectified.

I've stuck with the -000 format as it handles the 0 case better. In my case the point number, eg .001, is not a decimal but refers to a sub-level of a job number. So -001 is acceptable.


Dale Watson

What can I say? The effort you put in to assisting people in these forums is astonishing to say the least. Many, many thanks to you, and others like you, that help us all by spreading the knowledge that you have gained.

Chris

It worked yesterday.
It doesn't work today.
That's Windows!