How to format cells with multiple inputs if; and; or

[Patrick4704]

hi i am trying to set up a spread sheet for testing electrical circuits, but with changeable values. I have attached a sheet which I have started, on but can not complete, can anyone help. Please 

Thanks 

pat 

[#2]

@Patrick4704 

What is the problem? Why do you think the result in M11 is not correct?

[#3]

Hi Hans thank you for taken the time to have a look at this, the issue is that the values in roe E & F is different and has different set characteristics and therefor if the readings in roe I is greater than a set value pending on which type and what ampage it is it fails so not all MCB`s and RCBO`s are the same   @HansVogelaar 

Thanks again 

[#4]

@Patrick4704 

I'm afraid you'll have to explain in much more detail what you want the formula to do...

[#5]

Hi Hans in roes D i need to be able to change the Protective device to either Mcb or RCBO; in roe E i need to be able to change roe for a range of 6 to 100 these are Amps; in roe C i need to be able to change this to either B, C, or D; in Colum I the values changes ie. if I have a MCB and it type is B and the maximum set value for this is 2.6 say, if the reading that is imputed in this cell (I) is higher this is a fail. I need to be able to change the cells from D,E &,F, and pending on the set values of the type of device and its rating and also it Type(B,C, or D) as you see if i chance the cell E10 to a 20 and cell F10 ifs a fail because the set values will be different in Roe I.
Not sure if i am making sense, I kind a know its is a lot of code but unsure how to expand from what i have done in roe I-10
=IF(AND(D10="MCB",F10="B",E10=10,I10<=1.68),"Pass","Fail")

Thanks

[#6]

@Patrick4704 

You will have to use a list that specifies the limit for each protective device, each rating and each type. Do you already have such a list? If so, what does it look like?

[#7]

@HansVogelaar 

Hi Hans i have attached a copy of the set values 

Thanks 

[#8]

@Patrick4704 

Thanks. Am I correct in understanding that the device doesn't matter, only the type and rating?

[#9]

No, it will depend on if what is typed into the cell for the device whatever it will be either MCB or RCBO.

[#10]

@Patrick4704 

Then I don't understand. How are MCB and RCBO different in these tables?

 

[#11]

they are the same values but different devices

[#12]

@Patrick4704 

So the outcome of the formula doesn't depend on the device, does it?

[#13]

Hi Hans
No, it doesn't

[#14]

I think I was over complicating it

[#15 · Accepted solution]

@Patrick4704 

See the attached version. I left in columns N to P with some intermediate results that I used to create the formula in column M.

[#16]

you are a genus; cheers for that, I will need to change it to put it into a different template i have done all ready, i should be able to back trace from the sheet you done, is there any particular order i should work from.

Thanks a million

[#17]

@Patrick4704 

I'd start by redoing the additional columns from left to right, that should be simpler than trying to edit the final formula.

[#18]

Cheers thanks again