I have some some points (inputs and outputs) and on that basis I do a controller selection but I am facing problem at 1st stage as mentioned below:
For controller "ABC" points limit is:10
DO DI AI AO
7 1 2 0
For controller "XYZ" points limit is:=>17
DO DI AI AO
7 1 6 2
In the formula below it works until the limit of controller "ABC" That is 10 and limit of controller "XYZ" that is 17 is satisfied, as soon as, I insert additional points (either in DO,DI,AI,A0) for controller "XYZ", it shows "out of range". I can simply change "Out of range" to "XYZ" but I want to do it via formula.
=IF(AND(I29/D21<=17,E29/D21<=7,F29/D21<=2,G29/D21<=6,H29/D21<=2,(E29+G29+H29)/D21<=15,(F29+G29+H29)/D21<=10),"ABC",IF(AND(I29/D21<=17,E29/D21<=7,F29/D21<=2,G29/D21<=6,H29/D21<=2,(E29+G29+H29)/D21<=15,(F29+G29+H29)/D21<=10),"XYZ","Out of Range"))
The table is shown below.
A B C D E F G H I
1 EQUIPMENT Description QTY DO DI AI AO Total Points
2 INPUTS Any Input 1 7 2 6 2
3 XYZ Controller 1
4 Total Points 7 2 6 2 17
Here selection is Ok, as per the formula Controller XYZ is selected
BUT
ERROR: if any point is increased in E2/F2/G2/H2
A B C D E F G H I
1 EQUIPMENT Description QTY DO DI AI AO Total Points
2 INPUTS Any Input 1 7 3 6 2
3 XYZ Controller 1
4 Total Points 7 3 6 2 18
Please Help... BrExcel Question.jpg
Bookmarks