Always format numbers negative

[Bazman]

Hello,

I have a column, that when numbers are entered I need them to always show and calculate as negative. If the user enters 35 or -35 I would get the same display and the same result.

Is this possible ?

Thankyou Bary.

 

[Barrie Davidson]

Welcome to MrExcel!

Have you considered Data Validation? Using this you can force the users to enter negative numbers. From the main menu, select Data|Validation and then select "Decimal" or "Whole Number" (depending on your needs) in the "Allow" box. Complete the rest of the conditions and specify that the number must be less than zero.

Hope this helps.

 

[Bazman]

Thanks Barrie !

I did consider that, yes, but the users are very lazy and just want whatever they type to be a negative. I know, its only ONE extra key but aparently some of them forget to do it.

If there's no way to do this through formatting, I'll have to look at coding it.

Bary

 

[Barrie Davidson]

I would really shy away from that solution and force the users to ensure they've input negative numbers. Consistency is always better! (makes any changes, should they be required, that much easier to implement)

Having said that, you could use custom formatting for optics and then change the values to negative via a formula when performing your calculations. Formatting like:
_(* (#,##0.00);_(* (#,##0.00);_($* "-"??_);_(@_)

and then the formula would be something like:
=IF(A1<0,A1,-A1) & the rest of your formula

Hope this helps out.