IFS Formula with multiple conditions. Error: function in the formula causes the result to change each time the spreadsheet is calculated

[stellas]

I need big-time help here! I have altered the formula so many times and have gotten completely confused and still, get an error "The function in the formula causes the result to change each time the spreadsheet is calculated. The final evaluation step will match the result in the cell, but the interim steps may not."

Objective:
I am trying to get a cell to display specific words that are dependent on today's date and the dates entered in other columns.

Conditions:
Column B contains the formula that I want to change
Today's date (hypothetically Mar 11, 2022)
Condition 1: if K2 is blank, then return "ACTIVE", if it contains a date (any date), then "OFFER"
Condition 2: if M2 equals today and or + 1 day (Mar 11 or 12), then "FIRM", if no date then "OFFER"
Condition 3: if N2 equals today and or +1 day (Mar 11 or 12) then "SOLD FIRM", if no date then "FIRM
Condition 4: if O2 equals today, then "SOLD", if no date then "ACTIVE"

The formula:

=IFS(K2="","ACTIVE","OFFER",AND(M2<=TODAY(),"FIRM","OFFER",AND(N2<=TODAY(),"FIRM SOLD","FIRM",AND(O2=TODAY(),"SOLD","FIRM SOLD"))))

I really appreciate any and all help!

Thank you in advance.

 

[Alex Blakenburg]

Give this a try:

=IFS(K2="","ACTIVE",
        ISNUMBER(K2),"OFFER",
        M2=TODAY(),"FIRM",
        M2<=TODAY()+1,"FIRM",
        M2="","OFFER",
        N2=TODAY(),"FIRM SOLD",♥
        N2=TODAY()+1,"FIRM SOLD",
        N2="","FIRM",
        O2=TODAY(),"SOLD",
        O2="",ACTIVE,
        TRUE,"")

 

[stellas]

Give this a try:

=IFS(K2="","ACTIVE",
        ISNUMBER(K2),"OFFER",
        M2=TODAY(),"FIRM",
        M2<=TODAY()+1,"FIRM",
        M2="","OFFER",
        N2=TODAY(),"FIRM SOLD",♥
        N2=TODAY()+1,"FIRM SOLD",
        N2="","FIRM",
        O2=TODAY(),"SOLD",
        O2="",ACTIVE,
        TRUE,"")

Thank you but I still get an error #Name? What am I doing wrong?

 

[Fluff]

How about

=IFS(K2="","ACTIVE",OR(ISNUMBER(K2),M2=""),"OFFER",OR(M2=TODAY(),M2=TODAY()+1),"FIRM",OR(N2=TODAY(),N2=TODAY()+1),"FIRM SOLD",N2="","FIRM",O2=TODAY(),"SOLD",O2="","ACTIVE",TRUE,"")

 

[RoryA]

I think you want to change ACTIVE at the end to "ACTIVE" to remove the #NAME? error

 

[Fluff]

Good spot Rory, thanks .
I've corrected that in post#4

 

[stellas]

Good spot Rory, thanks .
I've corrected that in post#4

Thank you... the wording does not change past "OFFER" even though N is today's date and in theory, should say "SOLD FIRM".

 

[Fluff]

That's because you have a date in K2 & you said

Condition 1: if K2 is blank, then return "ACTIVE", if it contains a date (any date), then "OFFER"

 

[Alex Blakenburg]

Thank you... the wording does not change past "OFFER" even though N is today's date and in theory, should say "SOLD FIRM".

Based on the image you provided your initial logic doesn't actually make any sense.
a) The logic needs to be done in reverse to what you have
b) Using date = today & today + 1 doesn't make sense.
Today changes each day and it would mean that if something is SOLD today would be changed to not sold 1 or 2 days later simply because today is now 2 days later.
c) The logic you provided does not say what to do if there is a date in Rescind.
(firstly when only K & L have a date, does is go back to being "ACTIVE" or blank, what if there are dates entered in M or N or O)

Try this as a starting point.

=IFS(AND(O2<>"",O2<=TODAY()+1),"SOLD",
         AND(N2<>"",N2<=TODAY()+1),"FIRM SOLD",
         AND(M2<>"",M2<=TODAY()+1),"FIRM",
         K2="","ACTIVE",
         ISNUMBER(K2),"OFFER",
         TRUE,"")

 

[stellas]

Based on the image you provided your initial logic doesn't actually make any sense.
a) The logic needs to be done in reverse to what you have
b) Using date = today & today + 1 doesn't make sense.
Today changes each day and it would mean that if something is SOLD today would be changed to not sold 1 or 2 days later simply because today is now 2 days later.
c) The logic you provided does not say what to do if there is a date in Rescind.
(firstly when only K & L have a date, does is go back to being "ACTIVE" or blank, what if there are dates entered in M or N or O)

Try this as a starting point.

=IFS(AND(O2<>"",O2<=TODAY()+1),"SOLD",
         AND(N2<>"",N2<=TODAY()+1),"FIRM SOLD",
         AND(M2<>"",M2<=TODAY()+1),"FIRM",
         K2="","ACTIVE",
         ISNUMBER(K2),"OFFER",
         TRUE,"")

Once each day is achieved, A2 returns the word associated. Once O2 is achieved, it should stay "SOLD". If there is no date, then the previous word applies.

So, using the example, if todays date is Mar 17 (N2 would be triggered), then it should say "FIRM SOLD", if there is no date then it should say "FIRM". This is what I am having a problem with, I am not able to identify the logic because I keep going in circles

I want it to do the following:
If Accepted date is the same as today or later up to the Rescission date, then to return "OFFER", if no date then returns "ACTIVE". If the Removal Date is today or later up to the Deposit Date, then to return "FIRM", if no date then "OFFER". If the Deposit Date is today or later up to the Completion Date, then return "FIRM SOLD", if no date then return "FIRM". If the Completion date is today or later then return "SOLD", if no date then return "ACTIVE".

Does that make more sence? I'm not sure