• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Challenging Custom Format or Conditional Format

A

alarryaaron

New Member
This one is challenging.


I want a cell to be formatted as % if value <=100 and as currency if >100. [The context changes that if data is 100 or less, it needs to be treated as a percent; any other value should be treated as a real number.]


I tried using the following custom format:

[>100] $#,###.00;[<=100] 0.00%.


It works OK as long as I'm at 100 or less.


The problem is when I try to display numbers that are >100 and <10000 it shows as % (because it still interprets the typed number as a percent. For example, the number 9999 shows up as 9999%. I'd hoped to see it as $9,999.00


If number is >10000, it properly displays the $ sign but the value shown is 100 fold too small. If I type in 1234567, I get $12,345.67.


If this can't be done with Custom Formats, is there a way to do with Conditional Formatting? I couldn't get Conditional Formatting to do any formatting other than color changes.


Last resort is VBA.


Thanks in advance for your help.
 
Alarryaaron,

Or just use a formula

=IF(A2<100,TEXT(A2/100,"0.00%."),TEXT(A2,"$#,###.00"))
 
I you do use Hui's suggestion, be aware that the cells contain strings thereon not numbers, so you cannot do math with the value in that cell.
 
@XLD

Good point


Use this formula

=IF(A2<100,A2/100,TEXT(A2,"$#,###.00"))

and Format the cell as %.


Now you can link to that as either % or Number for further formulas
 
That's a brilliant formula but I can't use a formula in this instance.


The cell that gets typed into needs to interpret and display the correct format; that's why I was trying Custom Formats or Conditional Formatting since they affect the format of the cell that holds the typed-in value.


Any other guesses?
 
My last post does exactly that

Can you clarify what it doesn't do or what you need from it
 
Hi,


I think this would work.


Paste this in the custome format. [<=100]#"%";#


Hope this helps.


Cheers

VaraK
 
You just about nailed it. Just needed some zeroes and a special case to handle the value of zero as being dollars; not percent.


Here's what I ended up using:


[=0]$#.00;[<=100]#.00"%";$#.00


Thanks a bunch folks!
 
I have a similar question to this.


I'm displaying Actual v. Forecast on a dashboard, and by default the cell will show the variance in thousands. I added in a button that changes the underlying formulas to generate a percent, but when I do this the percent is in the thousands or more.


The catch here is that I'm using data bars, and trying several of the techniques above broke the data bar functionality. I'm considering using VBA to apply a % format, or my custom $$ format whenever a "Show Percentage" box is clicked. Any other ideas outside of VBA? Thanks.
 
Hi, Raesu!


Let me understand these points:

a) If Actual is 12.345,00 and Forecast is 45.678,00, you display 33.333 or 33 or 33K? (I use dot as digit separator and comma as decimal point).

b) If you select percentage, you display 370%, 270% or 3,7 or 2,7?


Regards!
 
You must log in or register to reply here.
Back
Top