+ Reply to Thread
Results 1 to 8 of 8

Return highest minus lowest value in a rng which is defined by location of a spesfic value

  1. #1
    Registered User
    Join Date
    02-12-2012
    Location
    Oslo, Norway
    MS-Off Ver
    Excel 2007, 2013
    Posts
    89

    Return highest minus lowest value in a rng which is defined by location of a spesfic value

    I've attached a dummy workbook to help explain better. Would appreciate if someone could take a look at it. Cheers
    Attached Files Attached Files
    Last edited by Test123Test; 03-14-2012 at 01:03 PM.

  2. #2
    Valued Forum Contributor smuzoen's Avatar
    Join Date
    10-28-2011
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2003/2007/2010
    Posts
    610

    Re: Return highest minus lowest value in a rng which is defined by location of a spesfic v

    I wrote this in between jobs so you will need to tidy it up - the concept will work - if you need help let me know
    Please Login or Register  to view this content.
    Hope this helps.
    Anthony
    Pack my box with five dozen liquor jugs
    PS: Remember to mark your questions as Solved once you are satisfied. Please rate the answer(s) by selecting the Star in the lower left next to the Triangle. It is appreciated

  3. #3
    Valued Forum Contributor
    Join Date
    06-17-2009
    Location
    Chennai,India
    MS-Off Ver
    Excel 2003,excel 2007
    Posts
    678

    Re: Return highest minus lowest value in a rng which is defined by location of a spesfic v

    returning the file "test1231test.....xls"


    macro is in the module
    appending macro here for general reference
    if you want to retest delete M3 down all the values and then again run "test"

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by venkat1926; 03-14-2012 at 03:19 AM.
    I am not an expert. better solutions may be available
    $$$$venkat1926$$$$@gmail.com

  4. #4
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Return highest minus lowest value in a rng which is defined by location of a spesfic v

    If you make your header row Row 4, you could use this formula in K2
    Please Login or Register  to view this content.
    and in M2
    Please Login or Register  to view this content.
    Drag/ Fill both Down.

    Or combine the formula to get the result directly.
    Please Login or Register  to view this content.
    What is the significance of the Search Length?
    I have assumed you want to analyse each weeks data. i.e. every group of 5 rows where the last in the group is a Friday.

    You say ....
    In this range, look then for the HIGHEST value present in the range, substract from the LOWEST value present in the range.
    Are you sure that will give you the result you need?
    Attached Files Attached Files
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  5. #5
    Registered User
    Join Date
    02-12-2012
    Location
    Oslo, Norway
    MS-Off Ver
    Excel 2007, 2013
    Posts
    89

    Re: Return highest minus lowest value in a rng which is defined by location of a spesfic v

    Wow, you guys are absolutely great I will take a look at all this later and let you know how it goes. Thanks again!

  6. #6
    Registered User
    Join Date
    02-12-2012
    Location
    Oslo, Norway
    MS-Off Ver
    Excel 2007, 2013
    Posts
    89

    Re: Return highest minus lowest value in a rng which is defined by location of a spesfic v

    venkat1926 I looked at the workbook you attached, unfortunately when I run the code it seems to pick up some thursdays and wednesdays as well, and sometimes it skips fridays. not sure how this is happening...

    Marcol, for your question, what I want to do is analyse the data starting with friday, and ending with "Search Length" number of rows from where friday is located...

    Smuzoen, I will look at your code a bit later

    cheers

  7. #7
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Return highest minus lowest value in a rng which is defined by location of a spesfic v

    Try this workbook, again no VBa required.

    In K5
    Please Login or Register  to view this content.
    In L5
    Please Login or Register  to view this content.
    Drag/ Fill both Down.

    Or combine the formulae to get the result directly, without a helper column.
    Please Login or Register  to view this content.
    Drag/ Fill both Down.

    Select your Start Day from the dropdown in K2, and enter the Search Length in L2.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    02-12-2012
    Location
    Oslo, Norway
    MS-Off Ver
    Excel 2007, 2013
    Posts
    89

    Re: Return highest minus lowest value in a rng which is defined by location of a spesfic v

    Marcol, thanks very much for this, it's working perfect! Left rep for you and others for their help...by the way how can I change the text in the dropdown from "monday", "tuesday", etc to my language which is "mandag", "tirsdag", I need to do this for proper functioning as the "day of week" column is returning names in norwegian.

    Thanks

    EDIT: Nevermind I figured it out. Thanks again for all your help, marking this thread as solved now.
    Last edited by Test123Test; 03-14-2012 at 01:02 PM.

+ 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