Changing my SUMPRODUCT/MAX Formula to only search the first x number of rows!

Hi,

I have the formula below which retrieves the highest value within a range but only the one of the values in the range (Column x) is a negative. The formula works fine but I now need to change it to only return the value from the first 26 rows of data that it finds.

My actual dataset contains multiple people each with >100 rows each, but I'm only interested in the first 26 rows.

Please Login or Register  to view this content.

I've had a try at offset or coutif but can't get it too work!

Any ideas?

Thanks in advance

Hi sixthsense and daddylonglegs,

I've uploaded the sample workbook and basically there are two issues. The Sumproduct formula looks down the list to find the highest value , but in some cases this is outside of the first 26 rows of data (like in the example), any values outside of the 26 rows should be excluded, which is where I thought where the offset would come in etc.

The second issue is the formula in Column W is returning the incorrect value if either the formula in Q is picking up data from outside of the 26, or if there are two identical values in the 26 rows (as it's picking the top one up but should be returning the value after the reduction).

I figure I could get around this second issue by having a helper column display in which period the highest point pre-reduction appears, and I could then offset by one similiar to column W to display the next period.

The dataset does have hundreds of lines for multiple people, however they are all sorted by column A and K, and therefore each dataset is together they are all together.

Any help you can offer would be appreciated.

Thanks

David

Thanks daddylonglegs,

That's a pretty amazing formula - very impressed. I've copied it into my workbook and it seems to work perfectly. Any idea how I can tell which period (Column K) that figure comes from (11/2013) in this case?

Cheers David

Hey daddylonglegs/sixthsense,

Sorry to repost but I still need some help solving the second part of the problem, namely finding the value of the cell 12 columns along from the value retrieved by the aggregate formula, well actually 12 along and 1 up (12/2013) in this case.

Is there any way to retrieve that value by modifying the Aggregate formula. I've searched online looking at retrieving which cell a value comes from but the best answer under formula auditing isn't feasible as I need it for each example in my calc sheet (60+)

Any help either of you can offer would be appreciated.

Thanks

Hey Guys, just to let you know I've posted the question in the general forum, I hope that's ok.

Thanks for the great assitance you've offered so far.

Thanks

David