Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

IF OR IF Possible?

0

Hey all, I've run into a scenario the calculator I'm building where I need to do one type of calculation if a drop down list is one value and another calculation if the drop down is another value.  I'm getting confused because it seems like I need something like IF(OR(IF))), but in my scenario I keep trying to put the logical test for the first IF statement as a string which would require the OR statement to be the true action which isn't what I'm trying to do.  Is what I'm attempting possible or do I need to separate this out?

Example english logic: 

If drop-down is "value1", where paramater1 >=X or parmeter2 is >=Y, set the cell to Z.

If drop-down is "value2", where paramater1 >=X or parmeter2 is >=Y, set the cell to Z.

Here is a snippet of what I have already working without the drop-down.

IF(OR(total_a>value1*12,total_b>=(value2*12)*0.8),"Configuration Not Supported",0)

TIA!

excel
MS Office Version: 2016
posted by: barneeze (rep: 2) on Sun Jul 29, 2018 at 10:55 am
Answer
Discuss

Answers

0

Set up your drop-down in A20. Give it two choices, "One" and "Two". Enter any four numbers in B20:C21. Then enter the formula below anywhere else on your sheet.

=IF(A20="One",B20*B21,IF(A20="Two",C20*C21,""))
  1. The formula examines A20.
  2. If it finds "One" it will calculate V20*B21 and show the result.
  3. If it doesn't find "One" it examines A20 again.
  4. If it finds "Two" it will execute C20*C21 and show the result.
  5. If "Two" isn't found either (the cell might be blank) the FALSE part of the second IF statement will be executed and "" shown.

I hope this solves your problem.

posted by: Variatus (rep: 4889) on Sun Jul 29, 2018 at 8:09 pm
Discuss

Discussion

The issue is that I still need the OR statement because if either of those calculations are >= then I want to set the cell to a particular number or give and error.  The example code I gave is what is currently working (the example above has "pseudo code" of course instead of my real values), I just need to associate that other condition somehow.  

If I use OR as the true condition then it will set the cell to the calculation and not evaluate the calcuations. Sorry for the confusion...does it make sense what I'm trying to do?  I'm starting to think I may have to start getting creative with an IF/AND/OR type formula.
barneeze (rep: 2) Jul 29, '18 at 8:24 pm
My example is very basic, intentionally. It gives you two separate calculations. Each of these could be a very complicated formula involving many IFs and ORs. The important thing is to treat them as independent. You can develop them separately, test them separately, and then copy them into the bigger scheme.
For better help more detail is needed. Your detail is very schematic and tainted by your idea of what the solution might look like. If my suggestion of two separate formulas embedded in a nested IF doesn't work the best way forward would be to post a workbook which shows exactly what you need.
Variatus (rep: 4889) Jul 29, '18 at 8:45 pm
Add to Discussion
0

What about this:

=IF(AND(OR(A1=1,A1=2),OR(A2>=X,A3>=Y)),"Z","Configuration Not Set")

A1 holds Value1 and Value2

A2 holds parameter 1

A3 holds parameter 2

posted by: don (rep: 1989) on Mon Jul 30, 2018 at 11:04 am
Discuss

Discussion

This is simliar to what I am looking to do, but was trying to understand whether or not I can figure out a way to have two different outcomes for Z in the example.  As you can see below it's actually just a part of a much larger formula.  I may end up having to just create two separate cells and have formulas for each.  Here are essentially the two different scenarios in their current state without the drop-down.

=IF(OR(total_vcpu>server_cpu*6,total_mem>=server_mem*6),"Configuration Not Supported",
IF(OR(total_vcpu>=server_cpu*5,total_mem>=server_mem*5),6,
IF(OR(total_vcpu>=server_cpu*4,total_mem>=server_mem*4),5,
IF(OR(total_vcpu>=server_cpu*3,total_mem>=server_mem*3),4,
IF(OR(total_vcpu<server_cpu*3,total_mem<server_mem*3),3,0)))))


VS.

=IF(OR(total_vcpu>server_cpu*6,total_mem>=server_mem*6),"Configuration Not Supported",
IF(OR(total_vcpu>=server_cpu*5,total_mem>=server_mem*5),7,
IF(OR(total_vcpu>=server_cpu*4,total_mem>=server_mem*4),6,
IF(OR(total_vcpu>=server_cpu*3,total_mem>=server_mem*3),5,
IF(OR(total_vcpu<server_cpu*3,total_mem<server_mem*3),4,0)))))
barneeze (rep: 2) Jul 30, '18 at 1:10 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login