Solved

Excel if true then use formula

Posted on 2014-04-02
13
319 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +1
13 Comments
 
LVL 8

Accepted Solution

by:
Naresh Patel 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: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
Independent Software Vendors: 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 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: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 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: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

Industry Leaders: 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!

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.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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 demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

617 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