Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel if true then use formula

Posted on 2014-04-02
13
Medium Priority
?
322 Views
Last Modified: 2014-04-02
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.
0
Comment
Question by:jay_eire
  • 6
  • 3
  • 2
  • +1
13 Comments
 
LVL 8

Accepted Solution

by:
Naresh Patel earned 2000 total points
ID: 39971744
Try this
=IF(C5=1,(D10+1)*Worksheet!BB1,IF(C5=2,(D10+1)*Worksheet!BF3,""))
0
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 39971748
if C5 = else then 1 or 2 it will return to "" (Blank)

Thanks
0
 
LVL 7

Author Closing Comment

by:jay_eire
ID: 39971758
Thank you sir
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Expert Comment

by:Naresh Patel
ID: 39971762
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39971766
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
 
LVL 34

Expert Comment

by:Rob Henson
ID: 39971769
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
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 39971776
Mr. Saqib Husain Syed,

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

Thanks
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39971792
Choose will return the second argument if c5=1,and the third argument it c5=2. This can be extended to 30 arguments.
0
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 39971796
Thank You
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 39971808
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
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 39971817
Thank you Mr.Rob H
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39971818
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
 
LVL 7

Author Comment

by:jay_eire
ID: 39971824
Thanks for all the replies folks.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question