Decimal Format dependant on values

[hruzek]

Hello all, I am new to the board. I found this board as I have undertake a very large Excel project. I am currently running Win 7 with Excel 2007.

History:
The user selects from drop down boxes a pressure transmitter range (examples of ranges: 0-100, 0-200, 0-600, 0-1000, 0-5000). I then have a VLOOKUP table that returns the high number to another set of cells so all I have to deal with is the 100, 200, 600, 1000 and 5000. I then use a COUNTIFS to tell me how many values are >= 1000.

Problem:
If any of the numbers are >=1000, then I want all of the ranges be formated without decimals. If all of the numbers are <1000, then I want them to be formated with one decimal place.

What I've tried:
Conditional formatting with a 2 formulas: 1) "Countifs cell value"<>0 then fromat number with 0 decimal place. 2)"Countifs cell valve"=0 then format number with 1 decimal place. This does work, but only for the first time you click it. After that it stops. I want it to be able to change incase the user changes the values.

Your help is greately appreciated.
Dustin

 

[escott]

Conditional format should work. First set the format to match the default. Then have an if statement in your conditional format that adjusts the format if any of the range meets the criteria.

 

[hruzek]

[SOLVED] Re: Decimal Format dependant on values

After you step away for a bit and they try again, sometimes you figure things out and feel like kicking yourself. Here's how I did it:


=IF("COUNTIFS CELL VALUE"=0,TEXT("Transmitter high range","0.0"),TEXT("Transmitter high range","0"))

Thanks.

 

[hruzek]

Conditional format should work. First set the format to match the default. Then have an if statement in your conditional format that adjusts the format if any of the range meets the criteria.

This is where I was running into the problem. It would format it once but would not change it again if the value was changed.

 

[jeffreybrown]

Try this...

For the range of cells set them to the decimals you desire, then...

...go to the conditional formatting and enter =A1>=1000 and then set the Format to a number with no decmals.

 

[hruzek]

Try this...

For the range of cells set them to the decimals you desire, then...

...go to the conditional formatting and enter =A1>=1000 and then set the Format to a number with no decmals.

That works too. It's cleaner because the other way I have to lock my "countifs" cell. I have to repeat this over 42 different entries.

Thanks.

 

[jeffreybrown]

Thanks for the follow up and glad it worked for you...you're welcome

 

[escott]

Thanks jeffreybrown. you explained in more detail what I was trying to say.