Is there any way to replace few no. using conditional formatting command ?

[jigar]

Hi,

I have 2 columns with 1500+ rows data, where I want to change numeric values > 100 with 100.

using if functions we can do, by sorting these 2 columns we can do but is there anyway where I can mix replace+condition function ?

 

[NARAYANK991]

Hi ,

I am not clear on what you want to do.

You say you :

want to change numeric values > 100 with 100.

Do you want to do this manually , by using the Excel menu options , or do you want to do it using a formula ?

Narayan

 

[Debraj]

Hi Jigar,

Welcome to the forum,

Conditional format are basically for Viewing purpose, not for calculation purpose.
Its just format, or applying mask on actual number.

Check the attached.. A1:A20 has a conditional format.. to display > 100 as 100..
Applied Conditional Format is ..
[Red][>100]"100";0;0

However the actual Number is still the same.. its not replacing, its just showing you in that format.

Replace is the only option.. wait.. in replace you dont have option to condition.. so use some trick..
If you greater than data has only 100 to 199, then..
Select the area, Use Find & replace

Find What : 1??*​

Replace with : 100​

​

Now replace all..

or you can filter All data having > 100, and you can select & manual Entering 100 in all cells at a single step.. (Ctrl + Enter)

 

[jigar]

Narayan K,

I want to use any formula in excel so that I can replace any values >100 with 100

thanks

 

[NARAYANK991]

Hi ,

If the column which has your data is column A , starting from cell A2 , then in any unused column put in the following formula :

=IF(A2>=100,100,A2)

or

=MAX(100,A2)

Copy this down. The unused column will now have 100 where ever the data in the corresponding cell in column A is greater than or equal to 100 , else it will have the value itself.

Narayan

 

[Hui]

Narrayan
If you use the second formula with a value of 101, it will return 101 not 100
it should be =MIN(100,A2)

 

[NARAYANK991]

Hi Hui ,

Sorry , my mistake.

Narayan

 

[jigar]

Hello Narayan and Hui..

thats true

based on your reply it seems excel doesnt have replace with conditional command.

by using above formula we can modify the no >100 , other way is to sort data and type 100 where value if greater than 100.

in excel we have replace options where we can replace particular numeric or text data but in the replace option we dont have any condition option

may be this feature we will see in next version of excel

thanks for your prompt feedback