If number in range was >= 100 and subsequently <100-CodePudding

Anyone got any ideas on how to do this?

I'm trying to build a spreadsheet that helps me monitor the performance of my blog articles. So if the article historically had >=100 visits at any point but subsequently gets <100 at any point I want to know about it.

The formula I've been playing with is:

=IF(((FILTER(C2:G2,C2:G2<>E2))>=100 AND (FILTER(C2:G2,C2:G2<>E2))<100, "Article Failing", ""))

I'm using Filter btw because I need to exclude column E, which is the delta between this month's & last month's numbers.

I know the formula isn't logically right but struggling to think of a way to do it.

Edit: Here's a link to the spreadsheet with desired output https://docs.google.com/spreadsheets/d/1TeaQ6oUbJDeKxUi8tvvCWXtw0oK9d5IVO60j1UbQCK8/edit?usp=sharing

Here's a table showing the sample data and desired output:

Total users (last 30 days)	Total users (prev 30 days)	Delta - Total users	Total users last 30-60 days	Total users prev 60-90 days	Delta - Total users	Above 100	Article Failing
651	90	-417	772	249	523	Tweak Article	Failing
610	570	40	550	432	118	Tweak Article	OK
436	409	27	328	210	118	Tweak Article	OK
422	288	134	53	288	-235	Tweak Article	OK
95	476	-90	417	477	-60	Below100	Failing
337	179	158	129	182	-53	Tweak Article	OK
305	395	-90	318	343	-25	Tweak Article	OK
304	348	-44	299	253	46	Tweak Article	OK
302	277	25	283	317	-34	Tweak Article	OK
286	252	34	268	281	-13	Tweak Article	OK
213	193	20	221	168	53	Tweak Article	OK
157	138	19	132	166	-34	Tweak Article	OK
150	157	-7	110	68	42	Tweak Article	OK

I've made cells B2 & A6 be failing articles i.e. they were >=100 but have since gone below 100. The end column 'Article Failing' is where I'm trying to create the formula.

Hope that makes things a bit clearer.

CodePudding user response：

This formula will match the desired results you show in the sample spreadsheet:

=if( 
  (max(A$2:A2) >= 100) * (A2 < 100) 
    
  (max(B$2:B2) >= 100) * (B2 < 100) 
    
  (row(B2) = row(B$2)) * (B2 < 100), 
  "Failing", 
  "OK" 
)