Return bottom value in subsets where criteria <> 0

Re: Return bottom value in subsets where criteria &lt;&gt; 0

I'm not a formula expert but this seems to do the trick. :cheers:

=INDEX($B$2:$B$26,SUMPRODUCT(MAX((ROW($A$2:$A$26)*(($E2=$A$2:$A$26))-1)*($C$2:$C$26>0))))

Re: Return bottom value in subsets where criteria &lt;&gt; 0

My pleasure, thanks for the feedback and reputation points. :cheers:

Re: Return bottom value in subsets where criteria &lt;&gt; 0

Another formula you may try to get the desired output...

=MAX(INDEX(($A$2:$A$26=E2)*($C$2:$C$26<>0)*($B$2:$B$26),))

Re: Return bottom value in subsets where criteria &lt;&gt; 0

Quote from rinconpaul;800422

Another good solution sktneer! In fact I'm kinda favouring your's more than the earlier one. There are some weird things happening with that formula on later trials, that I can't comprehend and getting an #N/A error? Seems more unstable? Thankfully you replied and all is good.

Maybe if you would care to share we could figure out the issue and all learn something.

Re: Return bottom value in subsets where criteria &lt;&gt; 0

You're welcome rinconpaul! Glad it helped.
And thanks for the rep.

Re: Return bottom value in subsets where criteria &lt;&gt; 0

Your formula should be like this...

In J9

=INDEX($F$9:$F$33,SUMPRODUCT(MAX((ROW($E$9:$E$33)-ROW($E$9)+1)*(I9=$E$9:$E$33)*($G$9:$G$33>0))))

Re: Return bottom value in subsets where criteria &lt;&gt; 0

Hello everyone ...

An alternative for cell J9 could be ...

=INDEX($F$9:$F$33,SUMPRODUCT(($E$9:$E$33=I9)*($G$9:$G$33>0))-1+MATCH(I9,$E$9:$E$33,0))

Hope this will help

Re: Return bottom value in subsets where criteria &lt;&gt; 0

This part, (ROW($E$9:$E$33)-ROW($E$9)+1), makes the difference. As you can see when you use Row() function in this context, all you want to get the row numbers of your data set.
And the above will give you an array of row numbers 1:25.
While your previous formula gives you row nummbers 8:32 as you used ROW($E$9:$E$33) and subtracted 1 from it. ROW($E$9:$E$33) will give you an array of numbers 9:33 and after subtracting 1 from it, you will get array of numbers 8:32.
And in J10 where you got #Ref! error, the problem was the length of Array used in Index part is 25 and your Row_Number returned is 29 which obviously is not found in the list of 25 items and that's why you were getting an error there.

The same formula worked in your previous sample file because there the data started from Row2 so you get row number array starting from 1.

Hope that's not very confusing as I am not very good in explaning things. Lol

Re: Return bottom value in subsets where criteria &lt;&gt; 0

Paul,

You are right ... explanations in the Forum are there to stay ... :wink:

The use of ROW() has two drawbacks :

1) It is dependent on the cell in which the initial formula is placed

2) It will give erroneous results ... should any rows be inserted within the range

To avoid these potential problems ... it is much safer to use ROW[COLOR="#FF0000"]S[/COLOR]()

Hope this will help

Re: Return bottom value in subsets where criteria &lt;&gt; 0

Here is another formula which you can try to get the same result.

Sample File1:

=INDEX($B$2:$B$26,MATCH(1,INDEX(($A$2:$A$26=E2)*($C$2:$C$26=0),),0)-1)

Sample File2:

=INDEX($F$9:$F$33,MATCH(1,INDEX(($E$9:$E$33=I9)*($G$9:$G$33=0),),0)-1)

Re: Return bottom value in subsets where criteria &lt;&gt; 0

Thanks for the feedback. :cheers: