Percentage Change

[Wayne Burritt]

Hi: I need to calculate percentage changes on the following:
current year ago
A1: -5 B1: 5

I need to calculate the percentage change from a year ago (B1) to current
(A1), if and only if they have the same sign. If either cells do not have
the same sign, I need excel to return "NA".

Thanks, Wayne and Heidi

 

[Paul]

Hi: I need to calculate percentage changes on the following:
current year ago
A1: -5 B1: 5

I need to calculate the percentage change from a year ago (B1) to current
(A1), if and only if they have the same sign. If either cells do not have
the same sign, I need excel to return "NA".

=IF(SIGN(A1)=SIGN(B1),(A1-B1)/B1,NA())

 

[J.E. McGimpsey]

If the OP wanted to return the string "NA", rather than an error:

=IF(SIGN(A1) = SIGN(B1), A1/B1 - 1, "NA")