SOLVED

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

Copper Contributor

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

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

17 Replies

# Re: How to format cells with multiple inputs if; and; or

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

# Re: How to format cells with multiple inputs if; and; or

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

# Re: How to format cells with multiple inputs if; and; or

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

# Re: How to format cells with multiple inputs if; and; or

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

# Re: How to format cells with multiple inputs if; and; or

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?

# Re: How to format cells with multiple inputs if; and; or

Hi Hans i have attached a copy of the set values

Thanks

# Re: How to format cells with multiple inputs if; and; or

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

# Re: How to format cells with multiple inputs if; and; or

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

# Re: How to format cells with multiple inputs if; and; or

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

# Re: How to format cells with multiple inputs if; and; or

they are the same values but different devices

# Re: How to format cells with multiple inputs if; and; or

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

Hi Hans
No, it doesn't

# Re: How to format cells with multiple inputs if; and; or

I think I was over complicating it
best response confirmed by Patrick4704 (Copper Contributor)
Solution

# Re: How to format cells with multiple inputs if; and; or

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.

# Re: How to format cells with multiple inputs if; and; or

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

# Re: How to format cells with multiple inputs if; and; or

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

# Re: How to format cells with multiple inputs if; and; or

Cheers thanks again

1 best response

Accepted Solutions
best response confirmed by Patrick4704 (Copper Contributor)
Solution

# Re: How to format cells with multiple inputs if; and; or

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.