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.
LVL 7
jay_eireAsked:
Who is Participating?
 
Naresh PatelConnect With a Mentor TraderCommented:
Try this
=IF(C5=1,(D10+1)*Worksheet!BB1,IF(C5=2,(D10+1)*Worksheet!BF3,""))
0
 
Naresh PatelTraderCommented:
if C5 = else then 1 or 2 it will return to "" (Blank)

Thanks
0
 
jay_eireAuthor Commented:
Thank you sir
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Naresh PatelTraderCommented:
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
0
 
Saqib Husain, SyedEngineerCommented:
You can also simplify this to

=(D10+1)*IF(C5=1,Worksheet!BB1,IF(C5=2,Worksheet!BF3,""))

or change it to

=(D10+1)*choose(C5,Worksheet!BB1,Worksheet!BF3)
0
 
Rob HensonFinance AnalystCommented:
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(C5,Worksheet!$A$1:$B$2,2,False),"")

Thanks
Rob H
0
 
Naresh PatelTraderCommented:
Mr. Saqib Husain Syed,

Will you please explain your formula =(D10+1)*choose(C5,Worksheet!BB1,Worksheet!BF3)
Just for My Knowledge.

Thanks
0
 
Saqib Husain, SyedEngineerCommented:
Choose will return the second argument if c5=1,and the third argument it c5=2. This can be extended to 30 arguments.
0
 
Naresh PatelTraderCommented:
Thank You
0
 
Rob HensonFinance AnalystCommented:
Slight clarification, if required.

Syntax =CHOOSE(Number,Option1,Option2,Option3,..,Option30)

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,Worksheet!BF3),"")

Thanks
Rob H
0
 
Naresh PatelTraderCommented:
Thank you Mr.Rob H
0
 
Saqib Husain, SyedEngineerCommented:
You might like to control the error condition with something like

=(D10+1)*CHOOSE(min(C5,2),Worksheet!BB1,Worksheet!BF3)

so that you always have an answer.
0
 
jay_eireAuthor Commented:
Thanks for all the replies folks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.