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]

Did you try the latest formula I sent you in post #9, to see if that is any closer to what you want ?

 

[stellas]

Did you try the latest formula I sent you in post #9, to see if that is any closer to what you want ?

I did thank you. I have included the image. It takes me to M2 but not responding to N2 date

 

[Alex Blakenburg]

Are you sure you copied in the whole in post #9 ?
Copy this into a blank sheet.

20220312 IFS Many Criteria stellas.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	STATUS										Accepted	Rescision	Removal	Deposit	Completion
2	FIRM SOLD										05-March-2022	09-March-2022	16-March-2022	17-March-2022	31-March-2022
3	FIRM										05-March-2022	09-March-2022	16-March-2022
4	OFFER										05-March-2022	09-March-2022
Formula

Cell Formulas
Range	Formula
A2:A4	A2	=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]

Are you sure you copied in the whole in post #9 ?
Copy this into a blank sheet.

20220312 IFS Many Criteria stellas.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	STATUS										Accepted	Rescision	Removal	Deposit	Completion
2	FIRM SOLD										05-March-2022	09-March-2022	16-March-2022	17-March-2022	31-March-2022
3	FIRM										05-March-2022	09-March-2022	16-March-2022
4	OFFER										05-March-2022	09-March-2022
Formula

Cell Formulas
Range	Formula
A2:A4	A2	=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,"")

IT WORKS, IT WORKS!!!!! THANK YOU THANK YOU THANK YOU!!!! I'm so thrilled!!! So grateful!!!

 

[Alex Blakenburg]

Always nice to have a happy customer . Glad we could help.