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!

 

[krodriguez]

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")

This works perfect thanks!!!

 

[Rick Rothstein]

This works perfect thanks!!!

Just wondering.... did you try the solution Tetra201 posted in Message #4 and reminded you about in Message #9 or the slight modification I made to Tetra201's formula in Message #10?

 

[krodriguez]

I did try both as they were more simple and was unable to get the expected results.

 

[Rick Rothstein]

I did try both as they were more simple and was unable to get the expected results.

I would be interested in seeing a few examples of where they failed.