Is there a way to limit "search area"?

[vinnypmd]

I'm trying to limit the number of columns searched through in a formul
that is looking for the lowest 5 values of the last 10 entries. I
this possible? For example, each row has columns B through D
available for data entry, however, I only want to find the minimum
values of the last 10 entries put in. Some of the columns can b
blank, so the last 10 entries may not be contiguous. I've trie
everything I can think of and then some, with no luck. An
suggestions?

Thanks!

Vinny P

 

[Bernie Deitrick]

Vinny,

The easiest way to do this is to insert four columns to the left of B, so
that your data is in F through DN. Then in column A, array enter the
formula (using Ctrl-Shift-Enter) in A1: (watch the line wrapping...)

=SMALL(IF((COLUMN($F1:$DN1)>=LARGE(IF($F1:$DN1<>"",COLUMN($F1:$DN1),0),10))*
($F1:$DN1<>""),$F1:$DN1,MAX($F1:$DN1)),COLUMN())

Copy to B1:E1, then copy down to match your rows.

HTH,
Bernie
MS Excel MVP