Excel forumula

Question

Excel forumula

Kapil Prasad 140

Conditions 1-3 are variables that can change with the increase of subsequent data. If the following conditions are met, they hold true:

Condition 1, N2>=1

Condition 2, M2<1

Condition 3, if any day in L2: B2>=1 and AG2<1

After all three conditions are met, it shows that it was completed yesterday

Emi Zhang-MSFT 30,201 Reputation points Microsoft External Staff

2024-01-01T08:34:47.13+00:00

Hi,

Just checking in to see if the information was helpful. Please let us know if you would like further assistance.

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer accepted by question author

Barry Schwarz 6,111

What is your question? Explain what the expression "if any day in L2" means.

IF(AND(N2>=1,M2<1,B2>=1,AG2<1),TODAY()-1,"NOT COMPLETE") but be aware that if you run this today and the conditions are met the result will be 30Dec23 and if you run it tomorrow the result will be 31Dec23.

0 comments

1 additional answer

Your answer

Emi Zhang-MSFT 30,201 Reputation points Microsoft External Staff

2024-01-01T08:34:47.13+00:00

Hi,

Just checking in to see if the information was helpful. Please let us know if you would like further assistance.

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 1

Aditya Tomar 5

The easiest way to handle this is by using the AND function for the first two conditions and COUNTIFS to check whether there is any day that meets the required criteria. For example:

=IF(AND(N2>=1,M2<1,COUNTIFS(L2:L2,">=1",AG2:AG2,"<1")>0),"Completed Yesterday","")

The AND function ensures that Conditions 1 and 2 are both met, while COUNTIFS checks whether the required condition is satisfied. If all three conditions are true, the formula returns "Completed Yesterday"; otherwise, it returns a blank.

0 comments