+ Reply to Thread
Results 1 to 4 of 4

IF formulas and lookup functions

  1. #1
    Registered User
    Join Date
    09-02-2013
    Location
    Oslo, Norway
    MS-Off Ver
    Excel 2013
    Posts
    44

    IF formulas and lookup functions

    Hi!

    In a series of data I would like to identify some specific points. I just made up a real simple table which is supposed to illustrate a jump cycle to try the math. I need to be able to identify three specific points
    -the start of the eccentric phase (which is when velocity becomes negative, IE the force drops from the original value)
    -start of flight time (which is when force is zero)
    -the end of flight time (which is on landing when force rises)

    So because the real data series in a jump like this includes well over 1000 values I simplified a series just to figure out the math so I would be able to check that I've done it right.

    I started out with trying to find the start of the eccentric phase. Because the force will vary a little bit before the jump is executed I figured that the point I should identify as the start of the eccentric phase is when "the force drops equal to or less than 90% of the original_value". Then I would be sure that the data I get is the start of this phase and not only small changes in the "weight".

    I would like excel to display the time point at which the above statement is true.

    I tried using a IF(OR.. formula to do the logical test for finding the value, but what I haven't been able to do is having excel display the time point. I can only get excel to display a text which tells me if this is true or not.

    My example table looks like this:

    Force (N) Time (ms)
    834 (original_v) 100
    700 234
    550 310
    0 395
    0 475
    0 585
    510 690
    2545 780
    7400 860


    So obviously the first value in this series which is equal to or less than original_value (834 N) - 10% is 700 N and the time point I would like Excel to display for me is at which time this is true, 234 ms.

    Anyone who could help me out here? I need to identify all the three points so if someone can help me out with this it would be great!!

    NB! If the table in the text doesn't work out, just download the attachment, it shows the table and data
    Attached Images Attached Images
    Last edited by anghicole; 09-02-2013 at 09:36 AM. Reason: The table and attachedment didn't work out as it shoud

  2. #2
    Forum Contributor
    Join Date
    03-12-2013
    Location
    Chicago, USA
    MS-Off Ver
    Excel 2007
    Posts
    230

    Re: IF formulas and lookup functions

    Check out the attached workbook.
    I solved the second two problems easily, but I had difficulty with the first. The way I solved the first problem is to create a third column that returns the time value if there was a greater than 10% decline. I couldn't find another way to solve for it.
    Attached Files Attached Files

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: IF formulas and lookup functions

    Hi and welcome to the forum

    If majosum's suggestion does not work for you, please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    09-02-2013
    Location
    Oslo, Norway
    MS-Off Ver
    Excel 2013
    Posts
    44

    Re: IF formulas and lookup functions

    @Majosum: Thank you a lot! This seems to work perfectly well!

    @FDibbins: Thank you! I'll remember that for next time

+ 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. Combining multiple functions>lookup/sum functions
    By mush106 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-22-2013, 07:47 AM
  2. [SOLVED] Help with multiple IF functions and possible lookup functions.
    By englishfellow in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-29-2013, 01:49 PM
  3. [SOLVED] Using formulas/functions
    By Lucylambkin in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-11-2005, 12:50 PM
  4. functions/formulas
    By AccountantPB in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-12-2005, 11:06 PM
  5. Functions and formulas
    By abfabrob in forum Excel General
    Replies: 4
    Last Post: 04-13-2005, 04:06 PM

Tags for this Thread

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