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