Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Excel if true then use formula

Posted on 2014-04-02
Medium Priority
320 Views
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
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
• 6
• 3
• 2
• +1

LVL 8

Accepted Solution

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

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

Thanks
0

LVL 7

Author Closing Comment

ID: 39971758
Thank you sir
0

LVL 8

Expert Comment

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

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

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

ID: 39971776
Mr. Saqib Husain Syed,

Just for My Knowledge.

Thanks
0

LVL 43

Expert Comment

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

ID: 39971796
Thank You
0

LVL 33

Expert Comment

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

ID: 39971817
Thank you Mr.Rob H
0

LVL 43

Expert Comment

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

ID: 39971824
Thanks for all the replies folks.
0

## Featured Post

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabilâ€¦
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns overâ€¦
###### Suggested Courses
Course of the Month11 days, 18 hours left to enroll