# Formula to scan group of four cells

1. ## Formula to scan group of four cells

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.

2. ## Re: Formula to scan group of four cells

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"

3. ## Re: Formula to scan group of four cells

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

4. ## Re: Formula to scan group of four cells

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?

5. ## Re: Formula to scan group of four cells

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

6. ## Re: Formula to scan group of four cells

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,"")

7. ## Re: Formula to scan group of four cells

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

8. ## Re: Formula to scan group of four cells

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.)

9. ## Re: Formula to scan group of four cells

You said a couple of different things.

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

10. ## Re: Formula to scan group of four cells

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

11. ## Re: Formula to scan group of four cells

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

12. ## Re: Formula to scan group of four cells

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'.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1