How to display N/A when using variance formula for + or - and - or + sings

[krodriguez]

Hello,

I have the following formula on A3 = A2/A1 basically what I want my formula to read is that if we have negative with positives numbers or viceversa in cells A2 or A1 give me a N/A results, if my signs on both cells are negative or positive give the result and not N/A

Thanks!

 

[Jon von der Heyden]

try:

=IF(SUMPRODUCT(SIGN(A1:A2)),A2/A1,NA())

 

[krodriguez]

Thanks the formula works but is shwoing #N/A how to remove # and just be N/A?

 

[Tetra201]

Maybe just

=IF(A2/A1<0,"N/A",A2/A1)

 

[Jon von der Heyden]

=if(sumproduct(sign(a1:a2)),a2/a1,"n/a")

 

[krodriguez]

Thank you! works perfect

 

[krodriguez]

the formula works when my cells are next to each other in this case A1:A2 =if(sumproduct(sign(a1:a2)),a2/a1,"n/a") that works
but it wont give the correct result if I have another number in between, example A1 = 1 A2 = -2 A3 = -2 if I want the result of A1 and A3 which should give me N/A (positive and negative sign) I get a number instead of N/A

 

[Jon von der Heyden]

Then you can:

1. Swap the SUMPROODUCT for SUM
2. Catch each cell reference in a SIGN function, and separate them with a comma

=IF(SUM(SIGN(A1),SIGN(A3)),A3/A1,"n/a")

 

[Tetra201]

Why such a complexity? If the numerator and denominator have different signs (the "N/A" case), the resulting ratio will be negative. Otherwise, the ratio will be non-negative. Thus the simple formula offered in Post # 4.

 

[Rick Rothstein]

Why such a complexity? If the numerator and denominator have different signs (the "N/A" case), the resulting ratio will be negative. Otherwise, the ratio will be non-negative. Thus the simple formula offered in Post # 4.

Agreed

Maybe just

=IF(A2/A1<0,"N/A",A2/A1)

If I am not mistaken, I think computers perform multiplication more efficiently than division. Given that, I might have written your formula this way...

=IF(A1*A2>0,A2/A1,"N/A")