+ Reply to Thread
Results 1 to 6 of 6

How to find a "big jump" or highs/lows in excel?

  1. #1
    Registered User
    Join Date
    09-13-2012
    Location
    United States
    MS-Off Ver
    Excel 2011 14.2.3 Mac
    Posts
    3

    How to find a "big jump" or highs/lows in excel?

    I am going to do my best to explain what I need here but I am really very novice with Excel. I have hunted the internet and this fabulous forum for answers but I am just not quite getting there. You see I have been tasked with finding the delta between highs and lows in some data I have. The data represents online messages over a particular period and a sentiment assignment. I can graph the volume and positive/negative/neutral sentiment (see attached) but what I am trying to do is ascertain what the actual magnitude is between peaks and dips so I can determine what are the really important changes comparatively. Essentially I am trying to identify which are the 'big enough' jumps to be of importance. Does that make any sense? Ugh.
    I have hourly totals, ratios, and change for each type of data: positive/negative/neutral.

    Ok, so the only response I have received is to tell me I titled my post badly. Sorry about that but I am not totally sure how to ask the question I am trying to ask. It is so frustrating to not have community help local to me. I appreciate that forums like this exist and I will keep looking for an answer - there has to be one out there!
    Attached Files Attached Files
    Last edited by velocio; 09-15-2012 at 05:37 PM. Reason: additional info

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Excel Idiot Needs Help! Finding Delta volume

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    Ben Van Johnson

  3. #3
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: How to find magnitude or delta highs/lows in excel?

    You need to define what classifies as "a big jump". I would use the %age change in sentiment to gauge this.

    So if +/-10% (or more) change is deemed a big jump, your formula in Cell I2 would be

    Please Login or Register  to view this content.
    and drag down

    Change the highlighted portion of the forumla accordingly for differing %ages.

    Does this help?
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  4. #4
    Registered User
    Join Date
    09-13-2012
    Location
    United States
    MS-Off Ver
    Excel 2011 14.2.3 Mac
    Posts
    3

    Re: How to find magnitude or delta highs/lows in excel?

    I am going to give that a try and I will repost if it works. !شكرا

  5. #5
    Registered User
    Join Date
    09-14-2012
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: How to find magnitude or delta highs/lows in excel?

    How are you defining a "high" or a "low"? Is it an absolute "high" (ie maximum value from the data set), in which case there would be one high and one low for each data set? Or are you looking for local minima and maxima? If so, you'll also need to define the size of each local region (ie the maximum value in x hours).

    Either way, once you've defined the above, then you can combine the IF function with a MIN or MAX function to make the calculation.

  6. #6
    Registered User
    Join Date
    09-13-2012
    Location
    United States
    MS-Off Ver
    Excel 2011 14.2.3 Mac
    Posts
    3

    Re: How to find a "big jump" or highs/lows in excel?

    I am trying to figure out what constitutes an important jump so I am not sure how I am defining highs and lows. You see, there are obvious peaks and valleys but I am trying to figure out what is important in relation to the volume of data, i.e. where the peaks and valleys are not so extreme could just be a function of low volume but could show just as large a spike/drop proportionately. I hope that makes sense. I am still trying to make this make sense to myself.

    The advice that Ace_XL gave me seems to work but I am trying to figure out how to assign the percentage change. I am not sure if I should use standard deviation or just the mean. Any more advice out there?

    This is so far beyond my feeble Excel skills, I do appreciate the help very, very much! Maybe someday I will be able to help someone else out!

+ 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