Can you do a conditional number format?

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

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

ken2step

Well-known Member
Joined
Jan 9, 2003
Messages
632
=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

Board Regular
Joined
Mar 14, 2002
Messages
80
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

Board Regular
Joined
Mar 14, 2002
Messages
80
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

Well-known Member
Joined
Jan 9, 2003
Messages
632

ADVERTISEMENT

jasonconlon said:
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

Board Regular
Joined
Mar 14, 2002
Messages
80
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

Well-known Member
Joined
Jan 9, 2003
Messages
632

ADVERTISEMENT

=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

Board Regular
Joined
Mar 14, 2002
Messages
80
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,257
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

Board Regular
Joined
Mar 14, 2002
Messages
80
I really appreciate the info, Tom.

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

Forum statistics

Threads
1,148,048
Messages
5,744,501
Members
423,881
Latest member
Nguyen Vu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top