Converting Negative Numbers to 0's

Hi all,

I'm looking to establish the most effective way of telling Excel where a value is negative, replace it with 0.

I've achieved this with a custom format but obviously this does not work where the data is required for formulas.

Equally i'm aware that I can make use of an IF() statement to decide if the data is less than 0 and use the appropriate value accordingly. However where i'm using values aggregated using a simple Sum() from other workbooks this quickly creates a very bloated formula, equally I dont want to have to replicate all my Worksheets that have negative numbers to contain a version where negatives are replaced with 0's.

I appreciate this sounds stupid...it's a reporting requirement unfortunately however. I'm trying to maintain a balance between keeping accurate data and generating reports with the negative numbers replaced (and replaced in any calculations).

Suggestions appreciated.

Cheers,

DM

Thats definately more efficient that my IF statement Thanks.

It still needs applying to every cell reference though, I know this is a big ask, but is there anyway to have it as a function thats looped through before the value is picked up for example?

Cheers,

DM

Originally Posted by DangerMouse

Thats definately more efficient that my IF statement Thanks.

It still needs applying to every cell reference though, I know this is a big ask, but is there anyway to have it as a function thats looped through before the value is picked up for example?

Cheers,

DM

If you can afford to use a macro then try the following :

Sub replace()
Dim k As String
Dim strt As Integer
Dim ends As Integer
k = InputBox("Please enter the Column Name where you want to replace. for example, A, B or C.", "Enter")
strt = 1
ends = Range(k & "65335").End(xlUp).Row
For i = strt To ends
If (Range(k & i).Value < 0) Then
Range(k & i).Value = 0
End If
Next
End Sub

Paste the code in ur vb editor and then run the macro...it will ask you the column name(A,B or C....etc) where you want to change the values!!!

Hope this helps :o)
Vikas B