LARGE with text string

[edwardpestian]

I have the following formula that works.

=INDEX(A34:D39,MATCH(LARGE($D$34:$D$39,4),$D$34:$D$39,0),1)


However when trying to change the the LARGE function to the SMALL function I am running in to difficulty. The reason for the problem is that the string the SMALL function is a text string. For example 4900-. 4900 with a trailing negative sign instead of a positive sign. I need the SMALL to take the two highest digits beginning from the left and then determine the smallest.

Thanks,

ep

[Domenic]

Try using a helper column where the text strings are converted to
numerical values, and then use this new column for your formula. For
example, enter the following formula in a column, let's say Column E,
starting at E34...

E34, copied down:

=IF(ISNUMBER(FIND("-",D34)),-SUBSTITUTE(D34,"-",""),SUBSTITUTE(D34,"+",""
)+0)

Then use this column for your formula.

Hope this helps!

In article
<[email protected]>,
edwardpestian
<[email protected]> wrote:

> I have the following formula that works.
>
> =INDEX(A34:D39,MATCH(LARGE($D$34:$D$39,4),$D$34:$D$39,0),1)
>
>
> However when trying to change the the LARGE function to the SMALL
> function I am running in to difficulty. The reason for the problem is
> that the string the SMALL function is a text string. For example
> 4900-. 4900 with a trailing negative sign instead of a positive sign.
> I need the SMALL to take the two highest digits beginning from the left
> and then determine the smallest.
>
> Thanks,
>
> ep

[edwardpestian]

Works Perfect!

Thanks Domenic.