Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom Number Formats | Excel Discussion (Misc queries) | |||
Custom Number Formats - Help | Excel Discussion (Misc queries) | |||
Custom number formats | Excel Worksheet Functions | |||
custom number formats | Excel Worksheet Functions | |||
Custom Number Formats | Excel Discussion (Misc queries) |