Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LARGE with text string
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 ------------------------------------------------------------------------ edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809 View this thread: http://www.excelforum.com/showthread...hreadid=560196 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LARGE with text string
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 , edwardpestian 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LARGE with text string
Works Perfect! Thanks Domenic. -- edwardpestian ------------------------------------------------------------------------ edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809 View this thread: http://www.excelforum.com/showthread...hreadid=560196 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find & Replace text format | Excel Discussion (Misc queries) | |||
Finding specific text in a string | Excel Worksheet Functions | |||
Text string and sum | Excel Worksheet Functions | |||
Remove last character of text string | Excel Worksheet Functions | |||
dynamic cell reference within a text string | Excel Worksheet Functions |