Help with IFS Please

[Cognito82]

I'm unsure if I'm doing this right.

This formula is entered into a follow up column and has 2 date columns as well as 2 y/n columns. Source table is from today formula(table).

=IFS(J3<=Table1[Today]-180,"No",E3<=Table1[Today]-365,"No",F3="Yes","No",D3="No","Yes",F3="No","Yes",TRUE,"Yes")

Most rows are working but I'm unsure if it's correct once I correctly fill all columns with data.

Edit: It was working but this formula has now populated all NO.

I have updated formula to this currently

=IFS(D2="No","Yes",F2="No","Yes",J2<=Table1[Today]-180,"No",E2<=Table1[Today]-365,"No",F2="Yes","No",TRUE,"Yes")

:crying_face:

[#2]

@Cognito82 

You've had 35+ views, no replies. I strongly suspect it's because it's not clear if you actually have a question, and, if so, what that question is.

• Your description of the organization of the data to which the formula refers is unclear at best, confusing for sure.
• Where the formula itself resides is not mentioned, so its relationship to the fuzzy data in their various columns is unclear.
• What the formula--if and when you get it working--is supposed to do, what its function is in the bigger scheme of things, is unclear.

My own firsthand experience is that if I'm not clear on what a conditional formula is supposed to be doing, the conditional formula will suffer from that lack of clarity. So you could help us help you if you back up and describe what's going on in everyday English, without reference to the formula; the formula will follow quite naturally after the bigger picture is clear.

[#3]

I'm trying to flag follow up contact to clients if conditions are/aren't met and dates fall beyond certain timeframes.
Y/n columns refer to active and key accounts(being D and F), dated columns refer to last service and last contact(being E and J).
I'm hoping to provide this formula so as if the accounts are not active, a follow up is flagged; if an account is active but the parameters with in the formula for dates fall out of range(ie =180 days and/or +365 days) a follow up is flagged.

[#4]

That helps. Even more helpful would be if you could post a copy of the actual (or a facsimile with real names removed) on OneDrive or GoogleDrive, with a link posted here that grants us access. That way we (I or somebody else here) could write and test a formula with some real data.

[#5]

@mathetes 

https://1drv.ms/x/s!Aui5lVRDPQjas2QgD4a9ihQevy7u?e=sLMzim

Hope this has worked??

I'm real new at the more int/adv features of excel.

Is the priority of these ifs where they exist in the formula?

Ideally if both were yes I'd like it to only run off the dated ifs, but the dated ifs would always have more weight. Does this make sense?

[#6]

@Cognito82 

Hope this has worked??

Assuming you're referring to the link to the OneDrive file: YES, it works.

I'm real new at the more int/adv features of excel.

So are we all at some point in our lives. Fortunately there's this site, as well as many others on the internet. You should (if you haven't already) check out the many YouTube channels with help on various Excel functions. I also find ExcelJet to be a valuable resource and commend it to you.

Is the priority of these ifs where they exist in the formula?

Yes. In the case of the IFS function, the function stops when it comes to a condition that is met. So you need to write them with that in mind, putting them in sequence.

Ideally if both were yes I'd like it to only run off the dated ifs,
but the dated ifs would always have more weight. Does this make sense?

Yes, it makes sense. But you are far more familiar than I with the data, and it's YOUR interpretation that should come more readily, so I need to rely on you to go through all those rows and determine if you're getting the result you need, and to identify where you're not and why. I assume, since you're coming here for help, that there are some rows that are not working, but which ones? What conditions aren't being evaluated as desired?

It would help, actually, if you created a "developmental" spreadsheet containing a limited number of cases, each designed to test a condition or two. Having the whole universe to run through just makes it harder to identify the anomalies. 

HINT: (and this is just a guess), it may be that the use of the AND function would help, if in fact there are conditions that are combinations of conditions. For example, to test if both D4 and F4 are "Yes" a syntax like this

=IFS(AND(D4="Yes",F4="Yes"),whatever.....