+ Reply to Thread
Results 1 to 18 of 18

Index/Match/Min/ABS Function needs to ignore one value in the index.

  1. #1
    Registered User
    Join Date
    09-13-2019
    Location
    Denver
    MS-Off Ver
    365
    Posts
    7

    Index/Match/Min/ABS Function needs to ignore one value in the index.

    Hello,
    I have attached a sample spreadsheet of the problem.
    The end goal I am trying to achieve is to get the absolute min next value to d21 that does not include e21 as an answer. However e21 still must remain in the index. Formula that is used works, but in certain circumstances e21 will populate as the answer. Formula is in I21.
    Correct answer that I am looking for is e57. Sheet formatting must remain the same. Must be ABS as as some correct answers will read negative due to date change.
    Thanks for the help!
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Index/Match/Min/ABS Function needs to ignore one value in the index.

    To deal correctly with "negative time" the formula needed is =MOD(end-start,1)

    However I am baffled by the formulae in column G. They are all over the place. Why? It makes for a lot of work to get change each one individually to get the data displaying correctly. So what's the underlying logic in use here, 'cos it all looks a bit random, at first glance.

    Why have you broken up the data with repeated header information? 42/43 73/74, etc. That is not a good idea. It is much better to leave them out have a continuous data set. I know you said the formatting can't be changes, but really... it would make more sense to think again.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    09-13-2019
    Location
    Denver
    MS-Off Ver
    365
    Posts
    7

    Re: Index/Match/Min/ABS Function needs to ignore one value in the index.

    That's actually what the end result of the problem will solve. Answer minus column E = Time between stages, G. Its frac sand oilfield data, the numbers are well's that are getting fracked. It wont make sense to many.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Index/Match/Min/ABS Function needs to ignore one value in the index.

    Hit CTRL and ¬ ... usually top left on QWERTY keyboards. Look at the formulae in G. There seems to be so pattern in what is being subtracted from what!!!

  5. #5
    Registered User
    Join Date
    09-13-2019
    Location
    Denver
    MS-Off Ver
    365
    Posts
    7

    Re: Index/Match/Min/ABS Function needs to ignore one value in the index.

    There is a pattern sometimes, but it goes all over the place. There are issues with wells so they will skip, or an operator will want to stack a well for a while. Long story short, pattern will never hold for a whole pad and change frequently. That's why I'm trying to come up with this solution.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Index/Match/Min/ABS Function needs to ignore one value in the index.

    Still here... and still confused. Can you explain slowly (think "bright 5 year old"...) why E57 is the correct answer!!!

  7. #7
    Registered User
    Join Date
    09-13-2019
    Location
    Denver
    MS-Off Ver
    365
    Posts
    7

    Re: Index/Match/Min/ABS Function needs to ignore one value in the index.

    Haha, right on. Ok, so stage 19 on well 550 was on 9/1/19 7:54. We are trying to find when the previous stage ended. That would be stage 14 on well 141 at 9/1/19 6:42. Which is the minimum value between d21 and Index E:E, excluding e21.

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Index/Match/Min/ABS Function needs to ignore one value in the index.

    Not there yet. That's an explanation for a nine year old. See column J. Those are the time differences between E and D21. Cell K3 = minimum. Looking down J, its at row 46... not 57??!!
    Attached Files Attached Files

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Index/Match/Min/ABS Function needs to ignore one value in the index.

    Forget it. I understand now!! All I need is the formula....

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Index/Match/Min/ABS Function needs to ignore one value in the index.

    Use this array formula:

    =INDEX($E$3:$E$139,MATCH(MIN(ABS(IF($E$3:$E$139<>E21,$E$3:$E$139)-D21)),ABS(IF($E$3:$E$139<>E21,$E$3:$E$139)-D21),0))

    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    09-13-2019
    Location
    Denver
    MS-Off Ver
    365
    Posts
    7

    Re: Index/Match/Min/ABS Function needs to ignore one value in the index.

    Sounds good, I'm going to bed for the evening(morning?), I have put around 20 hours into trying to solve this.. if you have it by the time i wake up and get back on my shift I will be stoked.

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Index/Match/Min/ABS Function needs to ignore one value in the index.

    Excel does that to you. To me, too. Your formula fermented in my head all day, while I was on the beach!!

    There are specific circumstances where the above formula doesn't work.

    This seems to work consistently!!

    =INDEX($E:$E,AGGREGATE(15,6,ROW($E$3:$E$139)/((ROW($E$3:$E$139)<>ROW(E21))*(AGGREGATE(15,6,ABS($E$3:$E$139-D21)/($E$3:$E$139<>""),1)=ABS($E$3:$E$139-D21))),1))

    Check it out thoroughly!! If it fails... shout. Otherwise...

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    09-13-2019
    Location
    Denver
    MS-Off Ver
    365
    Posts
    7

    Re: Index/Match/Min/ABS Function needs to ignore one value in the index.

    You're close! I have attached a new spreadsheet with a current job we are running with the formula ran down the whole job as it will be once completed. Check it out, there are a few random instances where it just doesn't work and i have no idea why. Looking though, I have kept the time between stages on this job accurate and the error seems to come when the time matches or exceeds and hour, which is interesting.. This sheet is much cleaner as I have not been messing with it for days on end. Really appreciate the work!
    Attached Files Attached Files

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Index/Match/Min/ABS Function needs to ignore one value in the index.

    My problem remains that your dataset is so large that I cannot possibly tell if this is correct. It LOOKS reasonable... Hopefully, your familiarity with it will enable you to spot if the numbers are right, wrong, or just plain daft!! If it's wrong, make up 10 rows AND NO MORE and add manually calculated correct answers.
    Attached Files Attached Files

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,382

    Re: Index/Match/Min/ABS Function needs to ignore one value in the index.

    The file is reporting as corrupt, Glenn (something that's been happening quite a bit lately on EF).
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  16. #16
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Index/Match/Min/ABS Function needs to ignore one value in the index.

    It doesn't matter, ali.... It was wrong anyway!!

    Pronghorn. Try this.....

    =IFERROR(IF(OR(E3="",ISTEXT(E3)),"",1/(1/AGGREGATE(14,6,$E$3:$E$200/((ROW($E$3:$E$200)<>ROW(E3))*(E3>=$E$3:$E$200)),1))),"")
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    09-13-2019
    Location
    Denver
    MS-Off Ver
    365
    Posts
    7

    Re: Index/Match/Min/ABS Function needs to ignore one value in the index.

    Hell yes!!! You did it!!
    Thanks so much man.

  18. #18
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Index/Match/Min/ABS Function needs to ignore one value in the index.

    Yee Haw!! Glad to help. And all without INDEX, MATCH, ABS and (almost) MIN. Once I understood exactly what you wanted... it was easy enough.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. INDEX MATCH to ignore blank cells in array
    By Leaflock in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-05-2019, 06:36 PM
  2. [SOLVED] INDEX MATCH SMALL but ignore duplicates
    By pareid in forum Excel Formulas & Functions
    Replies: 27
    Last Post: 07-30-2019, 12:18 PM
  3. [SOLVED] Possible Index/Match and/or Duplicate/Ignore??
    By mrsbrannon in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-27-2018, 01:11 PM
  4. [SOLVED] Ignore blanks in Index/Match
    By dazbear in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 01-25-2018, 03:55 PM
  5. [SOLVED] Ignore N/A in INDEX MATCH sum
    By KML1976 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-02-2016, 10:31 AM
  6. Index Match Function? Ignore blank rows - (rows without values)
    By jgray in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-26-2015, 01:12 PM
  7. [SOLVED] Ignore if NA in index/match
    By anon in forum Excel General
    Replies: 5
    Last Post: 06-16-2014, 09:32 AM

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