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

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

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 -

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 -

.