Logical & Lookup Formulas
Convert Numbers to 1 or 0 Binary Flags (--(B2>0), SIGN & SUMPRODUCT)
Transform arbitrary revenue, usage, or inventory quantities into clean 1/0 binary indicator variables using double-unary --(B2 > 0) and tally active periods across rows with SUMPRODUCT.
Why --(B2 > 0) beats SIGN(B2) whenever credits or negative adjustments appear!
Logistic regression feature matrices, SaaS active-month cohorts, and bonus-eligibility trackers often need to compress raw dollar amounts ($0, $1,420, -$50 refund, $85) into a clean binary indicator: 1 if active (> 0) and 0 otherwise! Beware using =SIGN(B2) blindly: while SIGN(1420) = 1 and SIGN(0) = 0, a -$50 credit memo produces SIGN(-50) = -1, quietly subtracting from your active-month total!
Using double-unary boolean coercion =--(N(B2) > 0) (or =IF(N(B2) > 0, 1, 0)) maps every positive quantity strictly to 1 and maps zero, negative adjustments, AND accidental text labels safely to 0! Across a 6-month customer spend row B2:G2, =SUMPRODUCT(--(B2:G2 > 0)) counts active billing months in a single cell without creating six extra indicator columns!
| Raw Cell Value | SIGN(Value) | --(N(Value) > 0) Binary Flag | Active-Month Interpretation |
|---|---|---|---|
| $1,420.00 Invoice | 1 | 1 | Active Billing Period |
| $0.00 Dormant Month | 0 | 0 | Inactive Period |
| -$50.00 Credit Memo | -1 (Corrupts Sum!) | 0 (Safe Zero!) | Not an Active Purchase |
Single-Cell Positive Activity Binary Indicator (1 or 0)
Uses N(B2) > 0 inside double-unary --(...) so positive numbers return 1 while zeros, negatives, blanks, and text strings safely return 0.
=--(N(B2) > 0)Count Active (> 0) Months Across a Multi-Column Row
Coerces boolean (B2:G2 > 0) inside SUMPRODUCT to tally strictly positive numeric cells across six monthly columns.
=SUMPRODUCT((ISNUMBER(B2:G2)) * (B2:G2 > 0))Try the example
Use the original values or change them to test your understanding.