3

The following initial situation:

I have numerical values in an Excel column. However, I would like to reformat them so that the possible range of values is discretised into 4 sub-ranges and a text is output instead of the concrete value. Only in the last sub-range should the number (#) be concatinated with additional text. That is:

] ∞, 0 [      => "Error"
[ 0 ]         => "---"
[ 1 ]         => "X"
] 1, ∞ [      => #"x"

Question:

Which pattern do I have to enter as type to achieve this required result?

Thank you very much for your help!

My current status:

So far I have read the official documentation and tried to orientate myself on its examples, but unfortunately without success. My last pattern looks like this:
[<0][red]"Error";[=0]"---";[=1]"X";[>1]#"x"
... but unfortunately this pattern is not accepted by Excel.

DevArchitectMaster
  • 115
  • 6
  • 5
    Use helper column. You can't format like that: "Each format that you create can have up to three sections for numbers and a fourth section for text. ;;;" – user11222393 Aug 06 '23 at 20:53
  • 4
    The purpose of formatting is to modify how a cell is displayed but not change the contents of the cell. Any formula elsewhere which refers to that cell is not affected by how the cell is formatted. Do you want to format your cells or do you want to actually change the value of the cell? – DMM Aug 07 '23 at 03:42
  • 1
    You can use a format to test up to two ranges of values plus a default e.g. [<0]"Error";[=0]"---";"X" but you would need to use conditional formatting to add the fourth condition if that's really what you want to do. – Tom Sharpe Aug 07 '23 at 07:37
  • Thank you very much for the many replies. My aim was really not to change the value of the cell, but only the view, due to the value behind it. @TomSharpe How would the conditional formatting look like for this requirement? It looks like the requirements do not support (my current) Excel version, so I built the following workaround - I reduced the 4 discrete ranges to 3 (=0, >1, and everything else, i.e. <0) and could then map them as follows: [=0]"";[>1]#"x"; "X" – DevArchitectMaster Aug 09 '23 at 11:17
  • If the format applied to the range of cells starting at B2 was [<0]"Error";[=0]"---";#"X" then in conditional formatting|Use a formula... add =B2=1 and set the format to "X" for the special case where B2=1 If you used your format [=0]"";[>1]#"x"; "X" and wanted to show negative values as errors =B2<0 and set the format as "Error" leaving positive values up to 1 as "X". – Tom Sharpe Aug 09 '23 at 13:17

0 Answers0