Converting values >9 into 0

[chriss]

Hi guys,

I want to convert any value greater than 9 in column "k" of a spreadsheet to "0". Can anyone share their wisdom?

Forgive my ignorance.... but is there a quick and easy way to ignore the negative sign, e.g. if a value (in a separate cell) is -6, I need it to display as 6?

Thanks in advance!!!

Chris

[davesexcel]

Hi guys,

I want to convert any value greater than 9 in column "k" of a spreadsheet to "0". Can anyone share their wisdom?

Forgive my ignorance.... but is there a quick and easy way to ignore the negative sign, e.g. if a value (in a separate cell) is -6, I need it to display as 6?

Thanks in advance!!!

Chris

Try this code

Please Login or Register  to view this content.

[stevebriz]

Part 1
If the you want to put the "0"in the a seprate cell then you can use the formula

Please Login or Register  to view this content.

If this not what you want let us know...


Part 2
Use the formula in the separate cell ( this takes the absolute value of the value in A1

Please Login or Register  to view this content.

HTH
Steve

[Glio]

Hi Chriss,

Without using VB you could use the formula IF(K1>9,0,K1) in a new column to convert any number above 9 to 0

I'm not sure whether your number that you want to convert might also be negative.

If it is a separate number then the formula =IF(K1<0,K1*-1,K1) in a new column will convert any negative number to a positive.

If the number might be negative and cannot be above 9 then formula IF(K1<0,K1*-1,IF(K1>9,0,K1)) works but not if the negative number is less than -9.

Hope that helps

[chriss]

Thanks for the replies guys.

Glio, I tried your formulas first and they worked perfectly.

Thanks for all the help!!!

Chris