Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

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

Copper 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 

 

Thanks 

 

pat 

17 Replies

@Patrick4704 

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 

@Patrick4704 

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
=IF(AND(D10="MCB",F10="B",E10=10,I10<=1.68),"Pass","Fail")

Thanks

@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?

@Hans Vogelaar 

Hi Hans i have attached a copy of the set values 

 

Thanks 

@Patrick4704 

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.

@Patrick4704 

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

 

S0325.png

they are the same values but different devices

@Patrick4704 

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 (Copper Contributor)
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.

 

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

@Patrick4704 

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

1 best response

Accepted Solutions
best response confirmed by Patrick4704 (Copper Contributor)
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.

 

View solution in original post