Stock count….

What formula could I use to identify whether a number in a column is -, 0 or +?

Thinking = if(a1<0,”Negative”, “Positive”) but how can I introduce into the same formula if a1=0, “No variance”)?

Thanks!

A nested IF will do it.

= IF(A1<0,”Negative”,IF(A1>0,”Positive”,”No variance”))

Or, a trick that may or may not be useful, is to do a custom number format so that it displays Negative/Positive/No variance but the value is still in the cell for other formulas to reference. Custom number format like so:

[<0]”Negative”;[>0]”Positive”;”No Variance”

In both cases be aware of Excel’s tendency to introduce tiny rounding errors into complex sums, meaning sometimes what should be a zero is actually very slightly adrift (around E-15) and doesn’t get treated as a zero.

if(a1<0,”Negative”,if(a1=0,”no variance”,”Positive”))

The question though is why you would want to (given text is good for almost nothing). Assuming it’s for ease of reading why not just conditional format the cells to colour code or something?

Use the SIGN function.

The Microsoft Excel SIGN function* returns the sign of a number. If the number is positive, the SIGN function will return 1. If the number is negative, the SIGN function will return -1. If the number is 0, the SIGN function will return 0.

* also exists in Google Sheets

Stoner’s answer might be perfect depending on the why…

Or using the SIGN suggestion and avoiding IFs:

=CHOOSE(SIGN(A1)+2,”Negative”,”No Variance”,”Positive”)

Or an alternative to SIGN:

=CHOOSE(IFERROR(ABS(A1)/A1,0)+2,”Negative”,”No Variance”,”Positive”)

(lunchtime, I’m killing time….)