Small amendment to formula that calculates number of cells since certain word appears in cell

[Ironman]

Hi

The below Cells A8623:B8631 are a small extract from Cells A12:B35795

The below formula returns the number of days' consecutive activity since the last cell with REST in Column B.

=MAX(A12:A35795)-AGGREGATE(14,6,A12:A35795/(B12:B35795="REST"),1) & " DAY EXERCISE STREAK"

In the below example the result is 4, which the above formula returns (in Cell A8)

4 DAY EXERCISE STREAK

What I'm looking for is an addition to the above formula (or an alternative formula that does the job) so if the result is either 0 or 1 then the result cell A8 shows the word EXERCISE instead of "0/1 DAY EXERCISE STREAK".

Many thanks!

Fri, 30 Jul 2021	REST
Sat, 31 Jul 2021	OTHER
Sun, 1 Aug 2021	Cullingworth Rd/Viaduct/ Stn Road/Old Allen Road Back Lane/Wilsden Rd/ Cottingley Rd/Lee Lane/ Cross Lane/Coplowe Ln/ Crack Lane/Main Street/ Harden Ln/Mill Hill Top/ Wilsden Rd/Mad Mile/ Greenside Lane (21/02/2020)
Mon, 2 Aug 2021	OTHER
Tue, 3 Aug 2021	REST
Wed, 4 Aug 2021	OTHER
Thu, 5 Aug 2021	Hallas Br/Down Bents Ln Harden Lane/Smithy Ln/ Lee Farm/Black Hills/ Golf Course/R down Beck Foot Lane/Wagon Lane/ Up LLC to cannon monument/Back to 3-Rise Locks & over Br/ Brown Cow/Main Road all the way back home (22/04/2007)
Fri, 6 Aug 2021	OTHER
Sat, 7 Aug 2021	OTHER

 

[Peter_SSs]

Try

=IF(MAX(A12:A35795)-AGGREGATE(14,6,A12:A35795/(B12:B35795="REST"),1)<2,"EXERCISE",MAX(A12:A35795)-AGGREGATE(14,6,A12:A35795/(B12:B35795="REST"),1)& " DAY EXERCISE STREAK")

 

[Ironman]

That's perfect Peter, thank you so much!

I bet you thought "that's an elegant formula" so you won't be surprised that it was your good self who provided it in Oct 2018

 

[Peter_SSs]

You're welcome.

... no, I didn't recognise the formula but I'm glad it is still being of use.