Greater than 1 or 2

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I have a column of numbers 1,2,3,5,6,7,8,10,11,12,14 etc.. and would
like to create a formula with an if statement that if the number in
the cell below is greater by 1 the result is x, if the number in the
cell below is greater by 2 the result is y.

Thanks

Tom
 
Try this...

Assume your numbers start in cell A1. Enter this formula in B2 and copy down
to the end of data in column A:

=IF(A1+1=A2,"x",IF(A1+2=A2,"y",""))
 
If your numbers are in A1, A2, A3, ... enter into B2:
=CHOOSE(A2-A1,x,y)
and copy down.

Regards,
Bernd
 
Missing the quotes:

=CHOOSE(A2-A1,"x","y")

That a nice solution if those are the only 2 conditions. Note that any
difference >2 returns an error.
 
=CHOOSE(A2-A1,"x","y")
That a nice solution if those are the only 2 conditions. Note that any
difference >2 returns an error.
Click to expand...

I did not get the impression that "x" and "y" were what the OP was actually
looking for; but, if those two letters were in fact what was being sought,
then this formula will suppress the error (nothing is returned if the
difference is not 1 or 2)...

=MID("xy",A2-A1+3*(A1=A2),A2-A1)

Rick
 
I did not get the impression that "x" and "y" were what the OP was actually
looking for
Click to expand...

After reading the post again I think you're probably right.

Another advantage of CHOOSE is it's easily expanded for additional
conditions (up to a point).
 
Another advantage of CHOOSE is it's easily expanded for additional
conditions (up to a point).
Click to expand...

My formula can be expanded quite easily too <g>

=MID("abcxyz",A2-A1+999*OR(A1=A2,A2=""),1)

Of course, this again assumes single character returns from the evaluation
are what is wanted. As long as the choice string will never be longer than
99 characters (which could obviously be increased if needed), just put the
choice string characters in between the quote marks. (Note I corrected a
minor error from my first posting that didn't affect the original outcome
given the choices proposed by the OP; and I made it more robust at handling
some error situations.)

Rick
 
The 999 inside the formula was supposed to have been changed to 99 to match
the text description I provided.

Rick
 
Back
Top