Excel if function?

  Newuser3477 14:54 08 May 07
Locked

In my first cell I will only have 5 possible values.
Either +1 or 0 or +1 or +2 or +3

If the value in cell A1 is +1 I want 1 to appear in B1
If the value in cell A1 is 0 I want 2 to appear in B1
If the value in cell A1 is -1 I want 3 to appear in B1
If the value in cell A1 is -2 I want 4 to appear in B1
Is there a function which will give me the desired result
Thank you

  HappyTrucker 15:15 08 May 07

Your examples seem to change between your first and 2nd paragraphs. Are the values +1,0,+1,+2,+3 or are they +1,0,-1,-2,-3. Doesn't really matter, but I'll use the "-" ones below. Just change them for "+" if needed.

It partly depends on whether your values in A1 are stored as numbers or text. i.e are they stored as 1,0,-1,-2,-3 or the text values "+1","0","-1","-2","-3"?

The basic version of the formula using Excel's IF function in B1 would be (and VoG may correct me here):

This assumes the values in A1 are stored as numbers.

=IF(A1="","",IF(A1=1,1,IF(A1=0,2,IF(A1=-1,3,IF(A1=-2,4,IF(A1=-3,5,"DEFAULT VALUE HERE"))))))

If your values in A1 are stored as text.

=IF(A1="","",IF(A1="+1",1,IF(A1="0",2,IF(A1="-1",3,IF(A1=-"2",4,IF(A1=-"3",5,"DEFAULT VALUE HERE"))))))

Hope this makes sense. It works on my PC, so should work on yours.

  HappyTrucker 15:17 08 May 07

PS:

The default value part could be anything. It will appear if anything other than a blank or your 5 values are present in A1.

  Abel 15:39 08 May 07

I'm sure VoG™ will have a much more succinct way of doing this, but this formula works:
=IF(A1="","",IF(A1=-1,1,IF(A1=0,2,IF(A1=1,3,IF(A1=2,4)))))

Abel

  Abel 15:41 08 May 07

Happy Trucker, you beat me to the post!

Abel

  Abel 15:43 08 May 07

Just noticed, I omitted to put the leading minus sign in.

Abel

  Abel 15:43 08 May 07

Just noticed, I omitted to put the leading minus sign in.

Abel

  Abel 15:44 08 May 07

Just noticed, I omitted to put the leading minus sign in.

Abel

  Abel 15:52 08 May 07

Sorry about the stammer, my ISP, Orange, appears to be having trouble.

Abel

  Newuser3477 20:00 08 May 07

Many thanks to all

  daba 23:11 08 May 07

Simple progression, try

=-A1+2

in cell B1

This thread is now locked and can not be replied to.

Surface Pro (2017) vs Surface Pro 4

20 groundbreaking 3D animation techniques

How to mine Bitcoin on Mac