Excel: better ways to express this as a formula

Question

x is an integer > 0

for x = 0, return 0; for all other x, return 1

solutions I have considered:

"IF(whatever)", obviously. Want to avoid it if possible, just cuz nested/lengthy formulas using them get messy.
"IFERROR(x/x, 0)"

Hey TBH I remembered the existence of "IFERROR()" just as I was writing this. That is probably the solution I'll use if no one posts anything better.

Just curious at this point.

TY!

-1 excluding IF(...) as a solution for a requirement stated as if x ... then ... else ... is just ridiculous
You state that x is an integer > 0, so why would you need to test if it is zero?

Kerrek SB · Answer 1 · 2011-07-01 20:01:33Z

up vote 1 down vote

How about the ordinary ternary IF clause:

IF(x = 0, 0, 1)

answered Jul 1 '11 at 20:01

	Sorry, I said no If()! ;) I wanted something shorter and cleaner just for readability's sake. I already have loads of functions in this sheet that are like If(foo,If(foo2,If(foo3...),...,...)" and I am trying to improve on that.Originally I was going to just use "x/x" and deal with the #Errors, but IFERROR seems like it can add a level of not-too-verbose polish to that which is why I said I'd most likely use that. – stuporuser Jul 1 '11 at 21:52
	Yes and sorry if my shorthand wasn't clear but the ternary was what I meant by "If(whatever)". There doesn't really appear to be an "If(whatever)"! I think "If(whatever,,)" is the simplest valid construction. But to be hyper-specific, when I originally wrote "whatever", what I actually meant was "logical_test, [value_if_true], [value_if_false]" ;) – stuporuser Jul 1 '11 at 21:56
	Well, you do want something piecewise, which you cannot construct out of analytic expressions like x/x alone: continuous functions of continuous functions are continuous. What's the problem with having an IF? Maybe a a MAX will do? – Kerrek SB Jul 2 '11 at 1:13
	`IF()` seems like the correct choice from a semantic point of view. Aren't you just trying to be clever for clever's sake otherwise? – Phydaux Jul 2 '11 at 12:23

feedback

Kaze · Answer 2 · 2011-07-02 11:29:27Z

=NOT(X=0)+0

where X can either be:

Example:
enter image description here

When you remove the "+0" you'll get either TRUE or FALSE.

2 Answers