Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

X is an integer > 0

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

Solutions I have considered:

  1. IF (whatever), obviously. Want to avoid it if possible, just because nested/lengthy formulas using them get messy.

  2. 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.

share|improve this question
    
-1 excluding IF(...) as a solution for a requirement stated as if x ... then ... else ... is just ridiculous –  chris neilsen Jul 2 '11 at 12:40
    
You state that x is an integer > 0, so why would you need to test if it is zero? –  James Aug 5 '11 at 22:14
add comment

3 Answers 3

=MIN(A1,1) seems self-documenting

share|improve this answer
add comment

How about the ordinary ternary IF clause:

IF(x = 0, 0, 1)
share|improve this answer
    
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? –  Chris Kent Jul 2 '11 at 12:23
    
Unless we are misunderstanding what it is you are looking for, from your question it appears you want one value if the value of 'X' is true and another if 'X' is false. This meets that requirement. –  Sarge Nov 19 '12 at 18:51
add comment
=NOT(X=0)+0

where X can either be:

  1. a reference to another cell
  2. a number
  3. a name referring to a cell or value
  4. a formula

Example:
enter image description here

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.