jay_eire
asked on
Excel if true then use formula
Hi I am using an if statement in excel when a cell value on another sheet is equal to 1 or 2 I want to change the cell value where my if statement is by running another formula but I get an error in excel saying I can use another =
=IF(C5=1, =(d10+1)*WorkSheet!BB1)), IF(C5=2, =(d10+1)*WorkSheet!BF3))
How can modify my if statement so that works as intended.
=IF(C5=1, =(d10+1)*WorkSheet!BB1)), IF(C5=2, =(d10+1)*WorkSheet!BF3))
How can modify my if statement so that works as intended.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you sir
your exact formula which returns to FALSE if C5 = is not equal to 1 or 2.
=IF(C5=1,(D10+1)*Worksheet !BB1,IF(C5 =2,(D10+1) *Worksheet !BF3))
Thanks
=IF(C5=1,(D10+1)*Worksheet
Thanks
You can also simplify this to
=(D10+1)*IF(C5=1,Worksheet !BB1,IF(C5 =2,Workshe et!BF3,"") )
or change it to
=(D10+1)*choose(C5,Workshe et!BB1,Wor ksheet!BF3 )
=(D10+1)*IF(C5=1,Worksheet
or change it to
=(D10+1)*choose(C5,Workshe
Rather than having an IF statement you can use a lookup table. Set up table like below:
1 BB1
2 BF3
I realise the BB1 and BF3 are cells but the table could link to those cells.
Assuming table above in Worksheet!A1:B2, the formula would then be:
=IFERROR((D10+1)*VLOOKUP(C 5,Workshee t!$A$1:$B$ 2,2,False) ,"")
Thanks
Rob H
1 BB1
2 BF3
I realise the BB1 and BF3 are cells but the table could link to those cells.
Assuming table above in Worksheet!A1:B2, the formula would then be:
=IFERROR((D10+1)*VLOOKUP(C
Thanks
Rob H
Mr. Saqib Husain Syed,
Will you please explain your formula =(D10+1)*choose(C5,Workshe et!BB1,Wor ksheet!BF3 )
Just for My Knowledge.
Thanks
Will you please explain your formula =(D10+1)*choose(C5,Workshe
Just for My Knowledge.
Thanks
Choose will return the second argument if c5=1,and the third argument it c5=2. This can be extended to 30 arguments.
Thank You
Slight clarification, if required.
Syntax =CHOOSE(Number,Option1,Opt ion2,Optio n3,..,Opti on30)
Number is the Option which will be chosen. So in your scenario if C5 = 1 then it will choose Option1, if C5=2 then it will choose Option2.
In your scenario, the CHOOSE would have to be included within an IFERROR to allow for other values in C5:
=IFERROR((D10+1)*CHOOSE(C5 ,Worksheet !BB1,Works heet!BF3), "")
Thanks
Rob H
Syntax =CHOOSE(Number,Option1,Opt
Number is the Option which will be chosen. So in your scenario if C5 = 1 then it will choose Option1, if C5=2 then it will choose Option2.
In your scenario, the CHOOSE would have to be included within an IFERROR to allow for other values in C5:
=IFERROR((D10+1)*CHOOSE(C5
Thanks
Rob H
Thank you Mr.Rob H
You might like to control the error condition with something like
=(D10+1)*CHOOSE(min(C5,2), Worksheet! BB1,Worksh eet!BF3)
so that you always have an answer.
=(D10+1)*CHOOSE(min(C5,2),
so that you always have an answer.
ASKER
Thanks for all the replies folks.
Thanks