+ Reply to Thread
Results 1 to 12 of 12

Formula to scan group of four cells

  1. #1
    Forum Contributor
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    291

    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.
    Last edited by terrypin; 09-05-2019 at 03:29 PM. Reason: TRying to remove image
    Terry, East Grinstead, UK
    Excel 365, Win 10 Pro, i7 PC, 4.0 GHz

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,659

    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. #3
    Forum Expert
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    5,082

    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. #4
    Forum Contributor
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    291

    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. #5
    Forum Contributor
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    291

    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
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    5,082

    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. #7
    Forum Contributor
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    291

    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. #8
    Forum Contributor
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    291

    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.)
    Last edited by terrypin; 09-06-2019 at 11:41 AM.

  9. #9
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,659

    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. #10
    Forum Contributor
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    291

    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. #11
    Forum Expert
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    5,082

    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. #12
    Forum Contributor
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    291

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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