Excel loop to change values of cells

[Echo toxin]

Howdy,

I have a column of numbers in Excel such as 56.0231, 0.02123, 23.0123, 19.93213, 34.5543, 99.4657 and so on... 30,000 of them in fact.

What I need to do is replace any cell in this column which has a value of less than 1 with 1.0 and any cell which has a value of greater than 99 with 99.0

The ordinary find/replace won't work because of the figures after the decimal points stored. I guess I need some kind of loop which goes through all the cells in the column checking to see if it meets the criteria and replacing the value otherwise leaving it alone.

But I don't know how to program this! Can anyone do it for me, purlease?

 

[Acolyte]

Probably good to implement a little bit of Visual basic to do it all for you.

Can you share the excel file with us? Or is it private e.t.c

 

[mark66]

There is no need to resort to VB when Excel can easily do it for you

Assuming values in column A, put this formula in B1 and fill down:
=MIN(99,MAX(1,A1))

Then copy and paste values over the cells in A and delete B.

Hope this helps.

 

[WIBSBOT]

There is no need to resort to VB when Excel can easily do it for you

Assuming values in column A, put this formula in B1 and fill down:
=MIN(99,MAX(1,A1))

Then copy and paste values over the cells in A and delete B.

Hope this helps.

Even though I'm not the original poster and will probably never use the above, that is a top quality answer. Have been using excel for years and would never have thought of something simple like that.

 

[Sic]

it's quite amazing the amount of people who don't realise the power of excel. pretty much anything you'd want to do with data, it's able to do. i only realised recently, what amazing capabilities it has!

 

[mark66]

Even though I'm not the original poster and will probably never use the above, that is a top quality answer. Have been using excel for years and would never have thought of something simple like that.

Cheers