[Solved] Multiple IF() conditions?

[jeandoll83]

Hi,

I need help on multiple if conditions?
Please see the attached file.. I need hel p in AC column.. conditions are inside the file also..

Thanks...

[acknak]

I defined a few names and came out with this:
=OR(AND(vendor="JCI";mttr<=3);AND(vendor="MDS";mttr<=4);mttr<=defcds)

I'm not sure this is a good solution; I prefer to have such arbitrary logic expressed as a table, but I couldn't see a simple way to do that. Maybe someone else has a better approach?

Here's mine:

[jeandoll83]

Thank you so much acknak... Its perfect.

[jeandoll83]

sorry acknak why its not working for other cells..? i tried to change MDS to JCI on C17 and C18 but the AC column is still the same..?

[Hagar Delest]

Here is what I would put:
=OR(AND(vendor="JCI";mttr<=3);AND(vendor="MDS";mttr<=4);AND(vendor<>"JCI";vendor<>"MDS";OR(AND(crity="C1";mttr<=4);AND(crity="C2";mttr<=8);AND(crity="C3";mttr<=24))))

Please add [Solved] at the beginning of the title in your first post (top of the topic) with the edit button if your issue has been fixed.

[acknak]

sorry acknak why its not working for other cells..? ...

Because my logic is wrong. The formula I gave accepts the default case where it should only check the special vendor.

Better go with Hagar's idea.

[jeandoll83]

hi guys!

Hi guys thanks alot.. its working...
Just one more thing..? Im wondering why i cant see the "yes" and "no" use in the formula..?
Can you explain...? so that next time I know how to do the same..? and change the result from yes to other words?
Thanks again...

[acknak]

... why i cant see the "yes" and "no" use in the formula..? ...

I prefer to avoid building formulas around text; numbers, or text that appears in a cell, are more robust. So the formula I suggested produces only numbers: 1 for "yes" and 0 for "no".

The text results you see on the screen are provided by the number format for those cells.

That means you can do calculations with those cells, if needed.

In your case, that may be over-complicated; you can skip it you like, just replace the ones and zeros in the formula by "YES" and "NO" or whatever.

[jeandoll83]

idont know how to do it..? :'( i cant see where to replace the 1,0 by yes and no...can you teach me to use the yes and no in the formula instead..? please..? thanks....

[Villeroy]

Format>Cells... tab "Numbers"

[acknak]

Just for fun, here's a version that works off of a table. The advantage of using a table is that you don't have to tweak a bunch of formulas if you need to change the rules, you just change the table. Also, you can easily see exactly what the rules and limits are.

The downside is that it's somewhat more complex and Calc still seems to have problems keeping all the named expressions straight: it doesn't seem to update everything correctly in some cases. A save/re-load seems to fix it.

@jeandoll83: if you find all this confusing, just ask for help, or just ignore it. Better to stick with what you understand.

[jeandoll83]

Thanks a lot guys...
but i have another concern? how will i incorporate blank cells in the formula..? and the result would either be "yes", "no" or "-"
please see attached file... both sheets..

thanks a lot..

[thinman3]

Try this: PM.I17 =IF(AND(E17="";C17="");"---";IF(E17<=C17;"YES";"NO"))

thinman3

[jeandoll83]

thanks thinman3..it worked for the second sheet... for the first sheet i need also...for the formula by Hagar Delest .. i need to add another result if cells are blank then " - " should be the result.. i would then have 3 results for the formula which are yes, no and - (or blank)

[thinman3]

Enter the formula below in C22 of Sheet 1 and then fill up & down......

=IF(C22="";"---";IF(OR(AND(vendor="JCI";mttr<=3);AND(vendor<>"JCI";OR(AND(crity="C1";mttr<=4);AND(crity="C2";mttr<=8);AND(crity="C3";mttr<=24))))))

For the "---" , you can edit it to have fewer - 's if that is more to your needs. Making it to indicate C22 is blank, just use
;""; to replace the "---" in the above formula.

thinman3