Solved

Excel if true then use formula

Posted on 2014-04-02
13
317 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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 33

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

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

820 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