Results 1 to 9 of 9

Thread: Time Intervals, Ranges, IF function, Lookup Function

  1. #1

    Time Intervals, Ranges, IF function, Lookup Function



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello All,

    I was hoping to get some help with ranges, lookups and time intervals.

    Table "Second Break Down" has every second listed (Col: N) and the individual (Col:O & P). The goal is to use the data from the "Time Adjusted Data" table to return a value of 1, 0 or -1 in the MBR column (Col: O) depending on which bowl that individual was in (Col: A). The way I want the formulas to work is as follows:

    IF:
    -Col:N is greater than or equal to any value in Col:J but less than the adjacent value (Col:K), but only if it has an adjacent value (therefore ignoring rows that values only in Col:J but not Col:K)
    -Col:D is equal to the value in O9


    Return:
    "1" if Col:A has a value of "R"
    "-1" if Col:A has a value of "W"
    "0" if the value in Col:N does not fall within any of the time ranges in Col:J and Col:K


    To explain this in longer terms:

    If the time value falls within any of the time intervals in the Time Adjusted Data table and that time interval belongs to the individual in question, I want the respective value of 1, -1 or 0 to display beside the second in question. Therefore when I graph this, I get for each individual's location throughout the entire experiment.

    For example, in Row 10, Col:J & K, we can see that MBR was on the "R" bowl (Col: A) from 00:00 to 2:42 seconds. Therefore in Col:O, I am hoping to see "1" repeating from 00:00 to 02:42. In row 15, the same thing except MLN was on the "W" bowl (Col: A) from 00:21 to 00:47, therefore in a Col:P, I would want to see "-1" from 00:21 to 00:47. Everything between Row 10 and Row 15 should be ignored since it doesn't have a value.

    As I mentioned above, the goal is to plot all the data of each individual to see their location over the entire experiment, ie. I want to see where MBR was at different times, either R Bowl, W Bowl or nothing; comparing this to the rest of the data we have collected (not shown here to help keep things simple).


    I hope that makes sense. Thank you.
    Attached Files Attached Files
    Last edited by Bob Phillips; 2014-07-24 at 01:16 AM.

  2. #2
    Is this what you mean
    Attached Files Attached Files

  3. #3
    Quote Originally Posted by Bob Phillips View Post
    Is this what you mean
    Nothing showed up on the excel sheet. I ended up using a SUMPRODUCT, very straight forward, I was just way over thinking the problem

  4. #4
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    719
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Nothing showed up on the excel sheet.
    Maybe Bob just knew when to invest time in your problem and when not to......

    http://www.mrexcel.com/forum/excel-q...two-dates.html

  5. #5
    Ya, Weazel came to the same conclusion my roommate did, use a SUMPRODUCT. But your comment good sir has lost me.

  6. #6
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    719
    Articles
    0
    Excel Version
    Excel 2010 64bit
    But your comment good sir has lost me
    You should read this http://www.excelguru.ca/node/7 it's also linked to in the rules over at MrExcel which you apparently overlooked.

  7. #7
    Is this the part where I tell you I tried to link the other post but your site won't let me till I reach "5 of something". I re-posted this after posting on Mr.Excel because I was under a time crunch today for my research and was also looking for differeing opinions. They were suggesting Index, Matches and I've been told there are a number of ways of doing this including SUMPRODUCTS, PIVOT TABLES and so forth.

    On an unrelated note, I am often asked to review something that are being reviewed by other individuals simultaneously; this is quite a normal practice in my field of academia and the benefits of this should be obvious.

    It is my understanding then that Bob replied with nothing on purpose. In my opinion this seems very unprofessional.

    Anyway, I am sorry I offended you so greatly and would prefer to remove myself as clearly I am not made to feel welcome. I can promise you I will post here no more and deactivate my account immediately to avoid any further problems. Take care.

  8. #8
    Good evening,

    I am unrelated to this post, so my opinion may not be valid, but anyway... What Nos is getting at is that if you are going to involve different large groups of volunteer help that you should let each of them know about the other. I doubt Bob posted a blank answer on purpose. He's very professional (not to mention a genius). However, at least from my view, it appears he was working on an answer for you a 3 am when your question had already been answered... This is a good forum with lots of very intelligent people floating about, but if you feel as though you have been wronged I guess that's your business. The people here want to help you, they just feel as though you are abusing their time when multiple sites are involved without knowledge of one another...

    Anyway, do with that what you will.

    Have a good night,

  9. #9
    For what it is worth now, the problem that was posted here seems to be significantly different to that posted at Mr Excel, I read it as needing some sort of double lookup. I couldn't see how to do that with formulae, so I created a VBA solution. I saved that workbook, as a .xlsm for the code, but then uploaded the original file instead of my amendment. I should have realised it as I change the filename to ExcelGuru - thread # - description, which is a lot different to what I uploaded - but I missed it. I put a fair bit of effort into this, if I had known he was cross-posting, I certainly wouldn't have bothered, and perhaps my upload error was serendipitous (for me ).

Posting Permissions

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