+ Reply to Thread
Results 1 to 8 of 8

Finding and returning the highest offset value in a list if the next contains negatives.

  1. #1
    Forum Contributor
    Join Date
    05-04-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    125

    Finding and returning the highest offset value in a list if the next contains negatives.

    Hi All,

    In an earlier post I received some fantastic help with an Offset index Match formula from member Tony Valko in returning Yes/No if a data-set contained negatives.

    I've now got the issue of taking it one step further and searching a data-set for negatives, and if negatives are found comparing the values to the left and offset by 1 below, and returning the highest of these!

    The data-set is employee data 61 rows long, however only the top 26 can be compared (fortnightly salary data).

    In the example I've attached I've highlighted values to be compared in yellow, with 2 out of 3 containing negatives. The last example doesn't have any negatives and could/would return a "" probably.

    I'm generally ok with index match, but I'm stumped at this and haven't found anything close on the forum.

    Any help would be appreciated.

    Thanks

    David

    Offset Negative.xlsx

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,771

    Re: Finding and returning the highest offset value in a list if the next contains negative

    In Y2 Cell

    =SUMPRODUCT(MAX(($A$2:$A$184=A2)*($B$2:$B$184=B2)*($X$2:$X$184<0)*($W$3:$W$185)))

    Drag it down...

    In Z2 Cell (If needed)

    =IF(AND(Y2=W2,X1<0),Y2,"")

    Drag it down...

  3. #3
    Forum Contributor
    Join Date
    05-04-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    125

    Re: Finding and returning the highest offset value in a list if the next contains negative

    Hi Sixthsense,

    Thanks for the response. I hadn't thought of using Sumproduct and in the example it works perfectly, however I've just realized that Sumproduct doesn't work across multiple sheets, and I have a summary sheet on Sheet2 which contains the layout :
    A B C
    ID Name Formula would go here.
    43 Emp1
    815 Emp50
    1047 Emp99

    Is there a solution to the Sumproduct limitation while still maintaining the formula?

    Thanks

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,771

    Re: Finding and returning the highest offset value in a list if the next contains negative

    Check the attached file and see whether it helps you
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    05-04-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    125

    Re: Finding and returning the highest offset value in a list if the next contains negative

    Hi, being honest not really, but it's getting late :-)

    I've got the following array formula but it doesn't seem to work either

    =MAX(IF((Sheet1!A2:A1000=$A$1)*(Sheet1!B2:B1000=$B$1)*(Sheet1!X2:X1000<0)*(Sheet1!W3:W184))

    Cheers

  6. #6
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,771

    Re: Finding and returning the highest offset value in a list if the next contains negative

    Sorry, it is not easy to give solution without seeing the sample workbook

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Finding and returning the highest offset value in a list if the next contains negative

    Not sure why you are having issues but SUMPRODUCT certainly does work across multiple sheets.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  8. #8
    Forum Contributor
    Join Date
    05-04-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    125

    Re: Finding and returning the highest offset value in a list if the next contains negative

    Hi Sixthsense/ChemistB,

    It transpires that I made a typo when testing the solution last night (it was late!). The solution works perfectly so many thanks for that.

    I'll mark the thread as resolved.

    Cheers

    David

+ 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. Returning greatest (absolute) value, including negatives
    By snguyen in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 04-08-2020, 09:41 AM
  2. Replies: 2
    Last Post: 06-26-2012, 10:01 AM
  3. Finding text in a list, and returning the row next to it.
    By Hejl82 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-22-2008, 02:26 PM
  4. Replies: 3
    Last Post: 05-01-2007, 06:25 AM
  5. Finding the next highest value in a list
    By mc32 in forum Excel General
    Replies: 4
    Last Post: 02-07-2006, 02:45 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