How to combine IF/AND Statements
Requirement:
The user has a worksheet that has to monitor two cells, F34 and F35 for separate values that are being compared back to E11 and L11. In addition, there is another cell, E29 that has a maximum value, so the calculation (((F50/1000)/E14)*1000) has to be compared to E29 and it greater than E29, cell value will be E29 value, otherwise will be the result of the calculation.
If F34 is "1" for instance and matches E11 value of "1", then the goal is to follow this IF/AND statement:
=IF(AND(F34=E11,(((F50/1000)/E14)*1000)>E29),E29,((F50/1000)/E14)*1000)
If F35 is "2" and matches L11 value of "2", then the goal is to follow this IF/AND statement:
=IF(AND(F35=L11,(((F49/1000)/E14)*1000)>E29),E29,((F49/1000)/E14)*1000)
The user right now, has them in two different cells, but would like to combine them into one statement in one cell - is this possible?
Solution:
=IF(F34=1,IF(AND(F34=E11,(((F50/1000)/E14)*1000)>E29),E29,((F50/1000)/E14)*1000),IF(F35=2,IF(AND(F35=L11,(((F49/1000)/E14)*1000)>E29),E29,((F49/1000)/E14)*1000),"N/A"))
Obtained from the OzGrid Help Forum.
Solution provided by NBVC.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to create and auto run macro if value on cell A1 is less than value on B2 |
| How to check if column header exists and if not then add column with that header |
| How to check values in a column and if condition matches then populate 3 other columns. |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
