To find the latest date

[Casey_0307]

Hi Everyone

I have a excel sheet that is setup in this way and we are not allow to change. May I seek the community's help on if it is possible to build a formula not only to check the value at the latest date, but if the the most recent date results is >8 then, then the outcome should be = 1, otherwise is should be 0.

E.g. If the most recent Date of Test is is 28-Dec2021 (i.e. Date of Test 4), the outcome we expect is 0 as the results of Test 4 is less than 8.

Date of Test 1	Results of Test 1	Date of Test 2	Results of Test 2	Date of Test 3	Results of Test 3	Date of Test 4	Results of Test 4
7-Jul-2021	7.8	8-Dec-2021	10	19-Dec-2021	7	28-Dec-2021	7

I am using Excel 2016. Thank you for looking into my post.

 

[Phuoc]

Book1
	A	B	C	D	E	F	G	H
1	Date of Test 1	Results of Test 1	Date of Test 2	Results of Test 2	Date of Test 3	Results of Test 3	Date of Test 4	Results of Test 4
2	07-Jul-21	7.8	08-Dec-21	10	19-Dec-21	7	28-Dec-21	7
3
4
5	0
Sheet1

Cell Formulas
Range	Formula
A5	A5	=--(LOOKUP(10^35,A2:H2)>8)

 

[Casey_0307]

Thank you very much. This does help solve my problem.

 

[Casey_0307]

Hi may I seek your assistance what if the table is not in ascending order. Like for example due to a human error the date of the latest results was entered in week 3 instead of week 4?

Date of Test 1	Results of Test 1	Date of Test 2	Results of Test 2	Date of Test 3	Results of Test 3	Date of Test 4	Results of Test 4
07-Jul-2021	7.8	08-Dec-2021	10	28-Dec-2021	7	19-Dec-2021	7

So what we we still want to see is the Latest Date: 28-12-2021 and the Outcome is 0 since it is less than 8.

Really appreciate your guidance.

 

[Phuoc]

Book1
	A	B	C	D	E	F	G	H
1	Date of Test 1	Results of Test 1	Date of Test 2	Results of Test 2	Date of Test 3	Results of Test 3	Date of Test 4	Results of Test 4
2	07-Jul-21	7.8	08-Dec-21	10	28-Dec-21	7	02-Dec-21	9
3
4
5	0
Sheet1

Cell Formulas
Range	Formula
A5	A5	=--(INDEX(B2:H2,MATCH(MAX(A2:G2),A2:G2,0))>8)

 

[Casey_0307]

Really very thankful for the help given. Thank you so much.
a. I am also asked if it is possible to change the ">8" to ">=7.1 and <=8"?
b. One more additional request : we want an Outcome "1" result if the latest Date BP is more than 140(sys) and more 90(dia). We would like to flag up this cases for review, and hence if there is a formula to let us know base on the latest BP date if the BP is 140(sys) and 90(dia)

BP#1 Date	BP(Sys)	BP(Dia)	BP#2 Date	BP(Sys)	BP(Dia)
7-Jul2021	140	90	8-Dec2021	120	80