Formula to scan group of four cells

[terrypin]

I'm struggling with this and would much appreciate some expert help please.

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.

FormulaToScanGroups.jpg

I've also uploaded an example of a full worksheet, which contains at least one other group that qualifies.

https://www.dropbox.com/s/34x1g5njn6...ple.xlsx?raw=1

NOTE: I changed my attached image but there now seem to be TWO images, although only a single ATTACH code is shown when I use the Edit tool. So I'm unsure how to remove the unwanted older image.

[63falcondude]

I don't want to download anything from other sites so I'm working off of the screenshot that you shared.

Try this in L2:
=IF(COUNTIF(K2:K5,"<0.4")=4,A2,"")
Drag the formula down column L.

If you want to upload an Excel workbook, the proper way to do it is as follows:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

[Fluff13]

Maybe
=IF(COUNTIF(K1:K4,"<0.4")=4, A1,"")
But your example doesn't fit as the first value is greater than 0.4

[terrypin]

Thanks both, appreciate the fast replies. I'll try those suggestions asap.

@Fluff13: My edited image and the edited text changed 0.4 to 0.6, but you must have replied so quickly that you missed it!

Anyone know how to delete that second image please?

[terrypin]

Couldn't get those (almost identical) formulas working. I got the error "Omits adjacent cells".


Attachment 640258

I then used the "Update..." option but that gave incorrect results. The example group I showed in my earlier post was not found. And the only entry that was made was also incorrect.

Attachment 640259

[Fluff13]

You're updated example still does not "fit", you say it should be less then 0.6, but you show a value of 0.6 being included

If you want less than or equal to, try
=IF(COUNTIF(K1:K4,"<=0.6")=4, A1,"")

[terrypin]

No, I said: "if any group has four numbers which are all less than or equal to 0.6,"

[terrypin]

I subsequently also posted in the microsoft.public.excel.misc newsgroup and had two solutions that worked fine. (Once I'd realised their German author uses a comma as decimal separator!)

=IF(COUNTIF(OFFSET(K1,,,4),"<=0.6")=4,ROW(),"")
or
=IF(COUNTIF(K1:K4,"<=0.6")=4,ROW(),"")

(The second looks almost identical to @Fluff13's latest.)

[63falcondude]

You said a couple of different things.

If it's ≤ 0.6 now, the formula posted in post #6 should work for you.

[terrypin]

Yes, thanks, it does. And you're right, my screenshot contradicted my text, sorry!

BTW, what are the little green triangles that appear at the top left of all the cells now in column L?

Attachment 640378

[Fluff13]

It's an "error" message, if you select a cell & hover the mouse over the exclamation mark that appears, it will tell you what the "error" is

[terrypin]

Thanks, understood. The message is "The formula in this cell refers to a range that has numbers adjacent to it." It's clearly not an error in this case. I've switched it off in File > Options > Formulas > Error checking rules by unchecking 'Formulas which omit cells in a region'.