Chris Cullen said:

I want to write a formula so that data in Column E will display 0% when

there is no value entered in Column B (a forecast ). I also want to keep

the

existing formula in Column E -calculating the percentage of change from

Column A (actual) and Column B (forecast).

Apparently you have some formulas ("keep the existing formula"). Please

post them. It might help to resolve the ambiguity of your English

explanation. No slight intended; English is an imprecise language for

explaining most mathematical relationships.

Assuming you want to calculate the percentage change row-by-row, and

assuming you really want the percentage change of actual over forecast (i.e.

"actual is x% of forecast"), then try:

=IF(B2="",0,IF(B2=0,SIGN(A2),(A2-B2)/B2))

That also addresses a problem you did not mention, namely: a forecast of

zero. There is no correct mathematical answer in that case. I arbitrarily

treat any actual positive amount as 100% gain over forecast, actual zero as

0% over forecast, and any actual negative amount as -100% of forecast.

PS: Personally, I would treat "no forecast" (B2 is empty or "") the same as

a forecast of zero. That is, I would use the arbitrary choices I made

above. To that end, the formula might be:

=IF(N(B2)=0,SIGN(A2),(A2-B2)/B2)

PS: If actual and forecast might have opposite signs(one negative, the

other postivie), the better formula might be:

=IF(N(B2)=0,SIGN(A2),(A2-B2)/ABS(B2))

----- original message -----