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

Occasional Contributor

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 





17 Replies


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

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   @Hans Vogelaar 


Thanks again 


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

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



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?

@Hans Vogelaar 

Hi Hans i have attached a copy of the set values 




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

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


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



they are the same values but different devices


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

Hi Hans
No, it doesn't
I think I was over complicating it
best response confirmed by Patrick4704 (Occasional Contributor)


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.


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


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

Cheers thanks again