Solved

Excel if true then use formula

Posted on 2014-04-02
13
314 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:
itjockey earned 500 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:itjockey
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
 
LVL 8

Expert Comment

by:itjockey
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 31

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 8

Expert Comment

by:itjockey
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:itjockey
ID: 39971796
Thank You
0
 
LVL 31

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:itjockey
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

762 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now