Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Inquiry
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Inquiry

    I have a bank of data that is updated monthly. I need to analyze said data to determine first all values above 35 then I need to look for two consecutive instances of this event. Once found, I would like to indicate for inspection if not ok

  2. #2
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,806
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Inquiry

    You may need to provide more info, but have a look at Autofilter for your "above 35"....that might also give you your consecutive instances
    Last edited by Michael M; Sep 19th, 2019 at 07:25 PM.
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  3. #3
    New Member
    Join Date
    Sep 2019
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Inquiry

    Thanks michael..can i do it in if functions?

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Inquiry

    Welcome to the MrExcel board!

    Quote Originally Posted by Duwin View Post
    ..can i do it in if functions?
    Probably, but we don't know what your data is like, where it is or exactly what you need. Could you give us a small set of dummy data and explain in realtion to that?
    My signature block below has help on how to do that.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #5
    New Member
    Join Date
    Sep 2019
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Inquiry

    Thanks peter..

    For example i have values from cell
    A1 15
    A2 35
    A3 50
    A4 20
    A5 10
    A6 55
    A7 70
    A8 65
    A9 50
    A10 35

    In A11 the logic must be when there is a consecutive cell with values equal or greater than 35..it must say "for inspection" ..if none it must say "okay"

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Inquiry

    Quote Originally Posted by Duwin View Post
    In A11 the logic must be when there is a consecutive cell with values equal or greater than 35..it must say "for inspection" ..if none it must say "okay"
    Okay, try this. I've given an example of each result.
    This is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}.

    Duwin

    AB
    11515
    23525
    35039
    42030
    51025
    65588
    7704
    86516
    95032
    103537
    11for inspectionokay

    Spreadsheet Formulas
    CellFormula
    A11{=IF(MAX(FREQUENCY(IF(A1:A10>35,ROW(A1:A10)),IF(A1:A10<=35,ROW(A1:A10))))>1,"for inspection","okay")}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  7. #7
    New Member
    Join Date
    Sep 2019
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Inquiry

    Thanks peter..it is a big help 🙂

  8. #8
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Inquiry

    Quote Originally Posted by Duwin View Post
    Thanks peter..it is a big help ��
    You're welcome. Thanks for the follow-up.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  9. #9
    New Member
    Join Date
    Sep 2019
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Inquiry

    Follow up question

    Given same data

    In a11 the logic must be when there is a cell with greater or equal to 35 and the consecutive cells are not yet zero, it must say "inspect" if not "ok"

  10. #10
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Inquiry

    Quote Originally Posted by Duwin View Post
    Follow up question

    Given same data

    In a11 the logic must be when there is a cell with greater or equal to 35 and the consecutive cells are not yet zero, it must say "inspect" if not "ok"
    If we have the same data how is the requirement different?

    What do you mean by "consecutive cells are not yet zero"?

    Perhaps we do need a couple more sets of sample data and the results?
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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