formula to allow only negatives

Hi,

Can someone help me on a formula query.

I want to force a cell to only allow negative values and convert any enter value to negative. In one cell (A1) there's a drop down for either Add or Remove and the next cell (B1) the values are added, if cell (A1) = 'Remove' then the value of (B1) must be negative or convert value to negative.

I hope I'm being clear, thanks for any help.

Mike

It works great, thanks for your help.

Mike

Hi oldchippy,

I've just notice a small problem on using this =IF(A1="Remove",B1*-1,B1), if someone remembers to put in a negative value as its a removal (they usually forget) it will convert the value back to a positive.

And it all seemed so simple.

Mike

Try this:

=IF(AND(A1="Remove",B1>0),B1*-1,B1)

Alternatively
If(A1="remove",-ABS(B1),ABS(B1))

=IF(A1="remove",SIGN(B1)*B1,B1) is an alternative.

Why not put Validation on B1?