Changing Formula to Value

  • I am trying to find and replace all negative values in an entire column (2000+ rows.) All the negative values are actually formulas of a couple other cells though so my find and replace isn't working since it only lets me find and replace formulas. Is there a way around this or a way to change that column into values only, not formulas? Thanks.

  • Re: Changing Formula to Value


    You don't say what you want to change the negative values to. If you want to change them to the same values, but positive, put =ABS( ) around your original formula and this will always calculate a positive amount.

    If you want to return zero or a blank cell if the result of the calculation is negative, then put an IF statement around your formula, e.g.

    =IF(A1*3<0,0,A1*3) [ for a zero result ]
    =IF(A1*3<0,"",A1*3) [ for a blank result ]

    Hope this helps.



Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!