Can you do a conditional number format?

[jasonconlon]

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.)

 

[Tom Urtis]

The code will work using 97 if the values (there are only two of them) are manually entered. You can still keep the data validation for B1, deselect the option for a drop-down arrow, and insert text in the Input Message tab for the user to enter either "Count" or "Percent" so that message pops up when B1 is selected. The code is not triggered in 97 for drop-down entry, but it is triggered by manual entry.

 

[jasonconlon]

You're a wiz, Tom.

Thanks a heap.