View Single Post
  #3   Report Post  
Old September 6th 19, 02:49 PM posted to microsoft.public.excel.misc
Terry Pinnell[_4_] Terry Pinnell[_4_] is offline
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2015
Posts: 192
Default Formula to scan group of four cells?

Claus Busch wrote:

Hi Terry,

Am Fri, 06 Sep 2019 10:31:58 +0100 schrieb Terry Pinnell:

I want to scan down a column of decimals and if any group has four numbers which are
all less than or equal to 0.6, then I want to copy a number from column A alongside
the first of those four cells. Hopefully my screenshot clarifies this.
https://www.dropbox.com/s/l7ik9km0re...oups.jpg?raw=1


Try:
=IF(COUNTIF(OFFSET(K1,,,4),"<=0,6")=4,ROW(),"")
or
=IF(COUNTIF(K1:K4,"<=0,6")=4,ROW(),"")


Regards
Claus B.


Thanks Claus, but neither seem to work.

No results are placed in col L for any valid set of four successive cells, such as
0.2
0.2
0.2
0.5
which should enter 2 in L2

Or the example I showed in my screenshot.

I think I could do it myself if you could tell me:
" how to construct a sort of 'combined IF'. Expressed at
its simplest, how would I write IF(This AND That are true, then do X, else do Y),
where This and That are values in separate cells?"

Something like this:
IF(K1<0.6 AND K2<0.6 AND K3<0.6 AND K4<0.6, A1, "")

I would then place the formula in L1, then copy that down the whole column. That
should do it, yes?

Best wishes,

Terry, East Grinstead, UK