Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Wox Wox is offline
external usenet poster
 
Posts: 11
Default Varied accuracy custom number formats

Hello,

I would like to create a custom number format that varies depending on the
value in a cell. Not sure if this is possible. Different volume liquid
dispensers that I work with have changing accuracies depending on the amount
to dispense. I would like a single number format that would take all of these
into account.

For a value of 0 = 0
0 to 2 = nearest even thousandth (1.002 or 1.004)
2 to 10 = nearest 100th
10 to 20 = nearest even 100th
20 to 100 = nearest 10th
100 to 200 = nearest even 10th
200 = nearest integer


I don't want the value of the cell to change, just the displayed format. Not
sure if this is even possible as it seems complex for number formatting.
Thanks for any advice.
-wox
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,856
Default Varied accuracy custom number formats

This custom format string doesn't quite give you what you want:

[200]0;[20]0.0;0.00;@

but you can play about with it to see if you can improve it. Numbers
get displayed like this:

300
30.0
3.00
0.30

and so on.

Hope this helps.

Pete

On Feb 24, 10:13*pm, Wox wrote:
Hello,

I would like to create a custom number format that varies depending on the
value in a cell. Not sure if this is possible. Different volume liquid
dispensers that I work with have changing accuracies depending on the amount
to dispense. I would like a single number format that would take all of these
into account.

For a value of 0 = 0

0 to 2 = nearest even thousandth (1.002 or 1.004)
2 to 10 = nearest 100th
10 to 20 = nearest even 100th
20 to 100 = nearest 10th
100 to 200 = nearest even 10th
200 = nearest integer


I don't want the value of the cell to change, just the displayed format. Not
sure if this is even possible as it seems complex for number formatting.
Thanks for any advice.
-wox


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,856
Default Varied accuracy custom number formats

I should have added that you only have 3 formats to play about with,
normally reserved for positive, negative and zero numbers, but you can
make these conditional, i.e. [200] relates to numbers that are above
200 (no decimal places), [20] for numbers above 20 (1 decimal place),
and all the rest are to 2 decimal places. Note the semicolons
separating these formats.

Hope this helps.

Pete

On Feb 25, 2:00*am, Pete_UK wrote:
This custom format string doesn't quite give you what you want:

[200]0;[20]0.0;0.00;@

but you can play about with it to see if you can improve it. Numbers
get displayed like this:

300
30.0
3.00
0.30

and so on.

Hope this helps.

Pete

On Feb 24, 10:13*pm, Wox wrote:



Hello,


I would like to create a custom number format that varies depending on the
value in a cell. Not sure if this is possible. Different volume liquid
dispensers that I work with have changing accuracies depending on the amount
to dispense. I would like a single number format that would take all of these
into account.


For a value of 0 = 0


0 to 2 = nearest even thousandth (1.002 or 1.004)
2 to 10 = nearest 100th
10 to 20 = nearest even 100th
20 to 100 = nearest 10th
100 to 200 = nearest even 10th
200 = nearest integer


I don't want the value of the cell to change, just the displayed format.. Not
sure if this is even possible as it seems complex for number formatting..
Thanks for any advice.
-wox- Hide quoted text -


- Show quoted text -


  #4   Report Post  
Posted to microsoft.public.excel.misc
Wox Wox is offline
external usenet poster
 
Posts: 11
Default Varied accuracy custom number formats

Thanks for the response Pete. Not quite what I am looking for, but close. I
suppose I can always do a code embed to format numbers like I want for each
cell. Makes the spreadsheet overly complex however, and I like to be as
efficient as possible.

"Pete_UK" wrote:

I should have added that you only have 3 formats to play about with,
normally reserved for positive, negative and zero numbers, but you can
make these conditional, i.e. [200] relates to numbers that are above
200 (no decimal places), [20] for numbers above 20 (1 decimal place),
and all the rest are to 2 decimal places. Note the semicolons
separating these formats.

Hope this helps.

Pete

On Feb 25, 2:00 am, Pete_UK wrote:
This custom format string doesn't quite give you what you want:

[200]0;[20]0.0;0.00;@

but you can play about with it to see if you can improve it. Numbers
get displayed like this:

300
30.0
3.00
0.30

and so on.

Hope this helps.

Pete

On Feb 24, 10:13 pm, Wox wrote:



Hello,


I would like to create a custom number format that varies depending on the
value in a cell. Not sure if this is possible. Different volume liquid
dispensers that I work with have changing accuracies depending on the amount
to dispense. I would like a single number format that would take all of these
into account.


For a value of 0 = 0


0 to 2 = nearest even thousandth (1.002 or 1.004)
2 to 10 = nearest 100th
10 to 20 = nearest even 100th
20 to 100 = nearest 10th
100 to 200 = nearest even 10th
200 = nearest integer


I don't want the value of the cell to change, just the displayed format.. Not
sure if this is even possible as it seems complex for number formatting..
Thanks for any advice.
-wox- Hide quoted text -


- Show quoted text -


.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Custom Number Formats Dickie Worton Excel Discussion (Misc queries) 1 February 5th 08 04:32 PM
Custom Number Formats - Help James Hamilton Excel Discussion (Misc queries) 3 May 16th 06 04:10 PM
Custom number formats Louise Excel Worksheet Functions 4 May 5th 06 08:57 AM
custom number formats Louise Excel Worksheet Functions 7 May 4th 06 12:30 PM
Custom Number Formats Mary Ann Excel Discussion (Misc queries) 8 July 11th 05 07:56 PM


All times are GMT +1. The time now is 08:51 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"