IF/OR

[sgpeters]

Hi,

I am hoping that someone can help me with this one, I don't think the experts will have too much trouble...

I am trying to create a formula where cell A1 is a dropdown with 4 values "Walk" "Run" "Mountain Cycle" "Road Cycle".

IF cell A1 is equal to "Walk" I need a number in cell B1 to multiply by 1.5. BUT
IF cell A1 is equal to "Run" I need a number in cell B1 to multiply by 1. BUT
IF cell A1 is equal to "Mountain Cycle" I need a number in cell B1 to divide by 2. BUT
IF cell A1 is equal to "Road Cycle" I need a number in cell B1 to divide by 3.

I am usually quite good with my trial and error method but I think I have confused myself.

Any help would be greatly appreciated.

Thanks,

Stu

 

[etaf]

do you want the actual value thats typed into b1 to be changed by the factor - where is the number in b1 entered
i think you will need VBA otherwise
If it can be in a different cell like C1 then you can use a NESTED IF() to use the factors based on A1
=IF( A1 = "walk", B1*1.5, IF( A1 = "Run", B1, IF ( A1 = "Mountain Cycle", B1/2 , If( A1 = "Road Cycle", B1/3 , "Error in A1", ))))
Do you still have excel version 2013 - as shown in your profile ? if so then a Nested IF , if the number can be put in a different cell
otherwise you need an expert here with VBA - not something i post solutions to currently, my VBA is from the 90's

 

[sgpeters]

I should have said, the formula would go into cell C1.

do you want the actual value thats typed into b1 to be changed by the factor - where is the number in b1 entered
i think you will need VBA otherwise
If it can be in a different cell like C1 then you can use a NESTED IF() to use the factors based on A1
Do you still have excel version 2013 - as shown in your profile ? if so then a Nested IF , if the number can be put in a different cell
otherwise you need an expert here with VBA - not something i post solutions to currently, my VBA is from the 90's

I should have said... The formula will go in Cell C1

 

[FormR]

Hi, welcome to the forum, here's one option you could try.

=CHOOSE(MATCH(A1,{"Walk","Run","Mountain Cycle","Road Cycle"},0),B1*1.5,B1,B1/2,B1/3)

 

[sgpeters]

=CHOOSE(MATCH(A1,{"Walk","Run","Mountain Cycle","Road Cycle"},0),B1*1.5,B1,B1/2,B1/3)

Thanks so much! That is perfect!

 

[bebo021999]

Withdraw