A have a numerical field with a custom number format which should accept a number (decimal between negative and positive infinity).
When a user wishes to, they may write something like "45%". This will automatically format the field to use the percentage formatting. A percentage acts sort of like (if not exactly like) a number in the range 0<=num<=1.
Let's call that column A. I wish to write a formula (e.g. column B) that treats the value of the cell differently depending on its formatting, without resorting to Apps Script. For example, if the cell in column A is 1234, the result should be 1234. If the cell in column B is 45%, it should equal 0.45*
Is there any simple way to do this with a function?
Things I thought of trying:
- coercing to a string, and checking for a "%" substring (doesn't work, oddly)
- some function that somehow reads the format (don't know of one)
- IF(or(0<=x,x<=1), ...) -- complicated by 0.45 being a valid entry as well as 45%.
Otherwise, it seems clear for correctness ("code smell"), if I want to allow number likes 0.45 to be equally valid as 45% and mean different things, I should create (yet another) column for this modality... if there's no way to distinguish them with a function, and I'm unwilling to use Apps Script for the latency it adds.