Varied accuracy custom number formats

[Wox]

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

 

[Pete_UK]

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

 

[Pete_UK]

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

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

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 ofthese
into account.

For a value of 0 = 0

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 -

 

[Wox]

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.

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

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

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 -

.