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

 

[ken2step]

=IF($B$1="Count",COUNT($A$1:$A$100),TEXT((SUM($A$1:$A$100)/COUNT($A$1:$A$100)),"0.00%"))

See if this is what you are looking for.

 

[jasonconlon]

Top answer, Ken. I wasn't aware of the TEXT() function. That definitely gives me the look/s that I want.

It also changes the data itself of course - from a value to text - which may affect further calculations using that result. But I can see I'll be able to get around that using the VALUE() function when referencing it.

Thanks heaps!

 

[jasonconlon]

Darn. That's not quite going to work for me.

While I can use VALUE() for general referencing, when it comes to charting the result unfortunately Excel won't plot 'text'.

My real-world application of this has a whole lot of those B2-type formula cells, which I subsequently want to chart.

The only way I can see around this is to separately return the results to values in other cells, and chart from these other cells - but I'm still stuck manually telling the chart that the Data Series it's plotting should be in a percent number format and not General, or vice-versa...

 

[ken2step]

Darn. That's not quite going to work for me.

While I can use VALUE() for general referencing, when it comes to charting the result unfortunately Excel won't plot 'text'.

My real-world application of this has a whole lot of those B2-type formula cells, which I subsequently want to chart.

The only way I can see around this is to separately return the results to values in other cells, and chart from these other cells - but I'm still stuck manually telling the chart that the Data Series it's plotting should be in a percent number format and not General, or vice-versa...

"0.00%" this part of the TEXT function changes it from text to numeric. I have not ever plotted a chart with this type results so I am sure what you say is true, I just not sure why. Maybe if you could supply some data with the Color HTML maker we can help you further.

 

[jasonconlon]

Sorry, Ken. That "0.00%" gives it a numeric look, but as far as Excel sees it, it's pure text after it's been through TEXT().

To test it yourself outside of charting, repeat the TEXT() version of the B2 formula in B3, and then in B4 try
=SUM(B2+B3)

You'll get a result of 0, and not the expected result of adding values which would give 133% or 1.33333

 

[ken2step]

=IF($B$1="Count",COUNT($A$1:$A$100),Value(TEXT((SUM($A$1:$A$100)/COUNT($A$1:$A$100)),"0.00%")))

Add the Value function to your formula....that may do it for you.

That worked for me.

 

[jasonconlon]

That works for turning it back into a number, but Excel also forgets the formatting applied by TEXT().

In other words, VALUE(TEXT()) cancel each other out, and you're left with what you started with anyways.

So yeah, I can use the TEXT() command to get the look; and then in some other cell I can use VALUE() to turn it back into its original number and chart on that number - but once you've gone VALUE() then you've lost the TEXT() formatting again and so you're stuck having to tell the chart what format you wanted in the first place...

 

[Tom Urtis]

The problem you are experiencing is due to the fact that formulas cannot change the physical Excel environment, such as formatting for column widths, row heights, and cell properties such as formatting as General or Percent. Even Conditional Formatting does not actually change the cell properties; CF only makes it look like it did, but really, it did not.

I think you need VBA for this. Here is one way to solve your problem, if you are using an Excel version later than (that is, *not*) Excel 97. So, if you are using Excel2000 or later, try this:

Right click on your sheet tab, left click on View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.



Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$1" Or Target.Cells.Count > 1 Then Exit Sub
If IsEmpty(Target) Then Exit Sub
Select Case Target.Value
Case "Count"
Range("B2").NumberFormat = "General"
Case "Percent"
Range("B2").NumberFormat = "0.00%"
End Select
End Sub

 

[jasonconlon]

I really appreciate the info, Tom.

But wouldn't you know it, our office is only running Excel 97.