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

1. ## 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

2. ## 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.``

3. ## 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.``

4. ## 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?

5. ## 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. ## 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. ## 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.

8. ## 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.

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

#### 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