Multiply by 3 if cell is "2" or "3" but not 1.

[oneyearsixmonths]

This seems like it should be simple..

All i want to do is if i put in 1 into a cell, that the result stays as 1
But if i put in 2 or 3 in a cell, the result is multiplied by 3.

Thanks

 

[Teeroy]

Hi and welcome to the Forum.

If you want to modify the value entered (in its position) then you need to use event driven VBA code.

An easier way to do this is to leave the value as entered then have another column (possibly hidden) that holds the multiplier.

For example if A1 holds your value, in B1 put the formula

B1: =IF(OR(A1=2, A1=3),3,1)

and you point to this multiplier from other referenced cells.

 

[oneyearsixmonths]

Hi and welcome to the Forum.

If you want to modify the value entered (in its position) then you need to use event driven VBA code.

An easier way to do this is to leave the value as entered then have another column (possibly hidden) that holds the multiplier.

For example if A1 holds your value, in B1 put the formula

B1: =IF(OR(A1=2, A1=3),3,1)

and you point to this multiplier from other referenced cells.

When I do this, it doesn't multiply the number by 3?
I'm happy with an extra column, is there no way there can just be *3 in the formula if the cell shows 2 or 3?

Thanks

 

[Teeroy]

OK, just to be clear, what is "the result"?

Is this the value in A1, or is it in another cell?

If the first you can use

B1: =IF(OR(A1=2, A1=3),3,1)*A1

or if the second you point to the cell that contains "the result" e.g. C1

B1: =IF(OR(A1=2, A1=3),3,1)*C1

 

[oneyearsixmonths]

OK, just to be clear, what is "the result"?

Is this the value in A1, or is it in another cell?

If the first you can use

B1: =IF(OR(A1=2, A1=3),3,1)*A1

or if the second you point to the cell that contains "the result" e.g. C1

B1: =IF(OR(A1=2, A1=3),3,1)*C1

That's brilliant! I used the first one..

Now, im sure this isn't possible but is there any way you could add to the formula " if the answer is 4 then minus 1 "

I think im pushing it haha, but with the results i want 4 cant be an answer, it will need to be changed to 3.

Thanks for all your help.

 

[Teeroy]

You can apply data validation to the cell so that it's value can only be 1-3.

Have a look at MS Excel Data Validation.