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

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

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

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

#### Thread Information

##### Users Browsing this Thread

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