jasonconlon
Board Regular
- Joined
- Mar 14, 2002
- Messages
- 80
Is it possible to set variable number formats via a function or conditional formatting in some way?
Here's the setup, with no formats set (i.e. all of a General number format) --
~ Cell A1 contains the value 1.
~ Cell A2 contains the value 0.
~ Cell A3 contains the value 1.
~ Cell B1 is list validated (no blanks allowed) to offer either "Count" or "Percent" only, and currently contains the value "Count".
~ Cell B2 contains the formula =IF($B$1="Count",COUNT($A$1:$A$100),SUM($A$1:$A$100)/COUNT($A$1:$A$100))
How can I set it so that B2 changes from a General format (which is fine for showing a 'Count' integer) to a percentage format when B1 is changed to "Percent"?
I can't see a Conditional Formatting option to do this.
I can't find a function that changes number formats.
Theoretically I could use a custom number format with condition operators (although I couldn't get this to work properly anyway) as:
[<1]0%;General
...but while this would change the number format for values between 0 and 1 to show as percentages, with other numbers in General format, there's still an issue for 0%-vs-0 and 100%-vs-1.
Any other ideas? (I imagine that B1 could be set to some sort of drop-down field with an associated macro, but I'd like to avoid macros if at all possible so that people don't have to deal with the macro warning; and also 'coz I'm not very familiar with programming them.)
Here's the setup, with no formats set (i.e. all of a General number format) --
~ Cell A1 contains the value 1.
~ Cell A2 contains the value 0.
~ Cell A3 contains the value 1.
~ Cell B1 is list validated (no blanks allowed) to offer either "Count" or "Percent" only, and currently contains the value "Count".
~ Cell B2 contains the formula =IF($B$1="Count",COUNT($A$1:$A$100),SUM($A$1:$A$100)/COUNT($A$1:$A$100))
How can I set it so that B2 changes from a General format (which is fine for showing a 'Count' integer) to a percentage format when B1 is changed to "Percent"?
I can't see a Conditional Formatting option to do this.
I can't find a function that changes number formats.
Theoretically I could use a custom number format with condition operators (although I couldn't get this to work properly anyway) as:
[<1]0%;General
...but while this would change the number format for values between 0 and 1 to show as percentages, with other numbers in General format, there's still an issue for 0%-vs-0 and 100%-vs-1.
Any other ideas? (I imagine that B1 could be set to some sort of drop-down field with an associated macro, but I'd like to avoid macros if at all possible so that people don't have to deal with the macro warning; and also 'coz I'm not very familiar with programming them.)