• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

find nearest value in a list with coma's

T

thomam1966

New Member
Hi i am trying to return a value in excel 2010.

i have a list of numbers in one cell separated by coma's (3,5,7,9,11,13)


i want to find the exact value or the next highest if the exact value isn't there.


for example a search for 4 would return 5 and a search for 7 would return 7


many thanks for your help
 
Hi ,


Do you want a solution using only formulae , or is VBA acceptable ?


If it is only formulae , can you use helper cells or do you want just one formula which will do whatever you want ?


Narayan
 
Welcome to Chandoo.org.


See if the following approach work for you.


Assuming that your data doesn't have closing brackets. i.e.

in Cell A1: 3,5,7,8,9,11,13

in Cell B1: 4 (criteria)

in formula cell CTRL + SHIFT + ENTER this formula (and not just ENTER):


=INDEX(--MID(SUBSTITUTE(","&A1,",",REPT(" ",100)),100*ROW($A$1:INDEX(A:A,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1)),100),MATCH(B1,--MID(SUBSTITUTE(","&A1,",",REPT(" ",100)),100*ROW($A$1:INDEX(A:A,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1)),100))+1)
 
Hi Shrivallabha ,


The formula is a great one , but it is not doing what I think the OP wanted :


1. The OP wanted the exact value to be found if it was present in the text string


2. The OP wanted the next higher value if the value to be found was not present in the text string


As it stands , the formula gives the next higher value consistently , even if the value to be found is present in the text string ; this is because of the +1 added to the MATCH result.


The problem is that the MATCH output will be the next higher value only if the array is arranged in descending order.


If the OP can change his data to a string of numbers in descending order , then a small change to your formula will give the result the OP wants :


=INDEX(--MID(SUBSTITUTE(","&A1,",",REPT(" ",20)),20*ROW($A$1:INDEX(A:A,LEN(","&A1)-LEN(SUBSTITUTE(A1,",","")))),20),MATCH(B1,--MID(SUBSTITUTE(","&A1,",",REPT(" ",20)),20*ROW($A$1:INDEX(A:A,LEN(","&A1)-LEN(SUBSTITUTE(A1,",","")))),20),-1))


I have used 20 instead of 100 because the number of elements in the text string is small. If the number of elements in the text string is more , the 20 can be increased to 50 ; for 20 elements 50 is sufficient.


However , even in this , if the value to be matched is greater than the maximum value present in the text string , the formula will give a #N/A error.


Narayan
 
Hi


If your data in A2 on Sheet1,


Define Nums


Refers to: =EVALUATE("{" &Sheet1!$A2&"}")


In B2,


=IF(LOOKUP(B1,Nums)=B1,LOOKUP(B1,Nums),MIN(IF(Nums>B1,Nums)))


Array formula.


Kris
 
Superb thinking, Krishnakumar. It is pity that Evaluate is not available as direct function and hence the acrobatics. However, I guess the formula can't be copied down as it uses defined range names.


Edit:= To continue with your thinking, we can put it in a VBA module like below and then use it directly:

[pre]
Code: 
Public Function Eval(Rng As Range) As Variant
Eval = Evaluate("{" & Rng.Value & "}")
End Function
[/pre]
Narayan, your concerns are correct. I should've checked my approach before posting. I did take general care of saying "see if the...".


I guess, following formula will work without hiccups (except that it'll throw 9e+307 instead of usual NA at user when the number is outside range).


=MIN(IF(--MID(SUBSTITUTE(","&A1,",",REPT(" ",100)),100*ROW($A$1:INDEX(A:A,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1)),100)>=B1,--MID(SUBSTITUTE(","&A1,",",REPT(" ",100)),100*ROW($A$1:INDEX(A:A,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1)),100),9E+307))
 
You can copy down the formula. I made the Column absolute and the row relative.


Evaluate is the part Excel4macro functions. So you can't use it directly in worksheet.


Edit: this formula can be copy down as well.


=IF(ISNUMBER(SEARCH($B$1,Nums)),$B$1,MIN(IF(Nums>$B$1,Nums)))


Again array entered.


Kris
 
Yes, you are right and thanks for the information. I get to learn so many things here everyday. I guess, your formula can be reduced down to:

=MIN(IF(Nums>=$B2,Nums))

and also the results aren't correct as Narayan has pointed out. The problem with first part of your formula design which handles searching numbers fails due to ARRAY entering. See below:

[pre]
Code: 
Data	               Num	Kris	Kris_Modified
3,5,7,8,9,11,13 	1	3	3
3,5,7,8,9,11,13	        2	3	3
3,5,7,8,9,11,13	        3	3	3
3,5,7,8,9,11,13	        4	5	5
3,5,7,8,9,11,13	        5	7	5
3,5,7,8,9,11,13	        6	7	7
3,5,7,8,9,11,13	        7	8	7
3,5,7,8,9,11,13	        8	9	8
3,5,7,8,9,11,13	        9	11	9
3,5,7,8,9,11,13	        10	11	11
3,5,7,8,9,11,13	        11	13	11
3,5,7,8,9,11,13	        12	13	13
3,5,7,8,9,11,13	        13	0	13
[/pre]
 
Hi thanks for the all the input to help me rectify this issue. with so many formulas, im confused as to which one should resolve my issue. i have copied and pasted all the excel formulas above and still get #N/A. i don't know VB. im just a regular excel user. also not sure what you mean by "in formula cell CTRL + SHIFT + ENTER this formula (and not just ENTER):". is this someting to make the formula run?


P.S, the maximum amount of numbers i will use in the text string is 24 as the numbers represent weeks in a 24 week cyle and some my be 1,2,3,4,5,6, through to 24


Thanks again.
 
Hi ,


You can use Shrivallabha's second formula , which is copied here :


=MIN(IF(--MID(SUBSTITUTE(","&A1,",",REPT(" ",100)),100*ROW($A$1:INDEX(A:A,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1)),100)>=B1,--MID(SUBSTITUTE(","&A1,",",REPT(" ",100)),100*ROW($A$1:INDEX(A:A,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1)),100),9E+307))


What you need to do is copy and paste this formula into any unused cell in your worksheet ; then , with the cursor in the same cell ( where you pasted the formula ) , press F2 , then press the CTRL SHIFT ENTER keys ( all 3 of them ) together i.e. press down on the CTRL and SHIFT keys , and press the ENTER key.


You will see that the above formula is now enclosed in the left and right curly brackets { and }.


The above formula is what is called an array formula ; a normal formula is entered into a cell by typing in the formula and pressing the ENTER key or any of the cursor movement keys ; an array formula is entered by pressing the CTRL SHIFT ENTER keys together.


Narayan
 
Hi,

Here is one more option, taking advantage of the SEARCH() function.


=MIN(IF(ISNUMBER(SEARCH("," & ROW($A$1:INDEX(A:A,24)), "," & SUBSTITUTE(A1, " ",""))), IF(ROW($A$1:INDEX(A:A,24))>=B1, ROW($A$1:INDEX(A:A,24)))))


Entered with Ctrl+Shift+Enter


A1 contains the comma delimited string of numbers, and B1 contains the number being sought.


It returns the matching number or next highest value for all numbers upto 24. Any greater value returns a zero. Adjust the max returned value from 24 to any other number as needed.


Cheers,

Sajan.


EDIT:

Formula has been tweaked to handle the delimiter and extra spaces, and not return false positives. It now returns a value upto the max in the source string, and returns zero for any number larger than that max value.
 
Hi Shrivallabha,


Here is the data set I used:

[pre]
Code: 
SourceString	                      Criteria	Result
24,1  ,  2,   9,   14, 7,17,13,5, 19	1	1
24,1  ,  2,   9,   14, 7,17,13,5, 19	4	5
24,1  ,  2,   9,   14, 7,17,13,5, 19	5	5
24,1  ,  2,   9,   14, 7,17,13,5, 19	23	24
24,1  ,  2,   9,   14, 7,17,13,5, 19	24	24
24,1  ,  2,   9,   14, 7,17,13,5, 19	6	7
24,1  ,  2,   9,   14, 7,17,13,5, 19	2	2
24,1  ,  2,   9,   14, 7,17,13,5, 19	3	5
24,1  ,  2,   9,   14, 7,17,13,5, 19	5	5
24,1  ,  2,   9,   14, 7,17,13,5, 19	21	24
24,1  ,  2,   9,   14, 7,17,13,5, 19	-1	1
24,1  ,  2,   9,   14, 7,17,13,5, 19	25	0
24,1  ,  2,   9,   14, 7,17,13,5, 19	18	19
[/pre]
I put the formula in cell C1 and copied down:

=MIN(IF(ISNUMBER(SEARCH("," & ROW($A$1:INDEX(A:A,24)), "," & SUBSTITUTE(A1, " ",""))), IF(ROW($A$1:INDEX(A:A,24))>=B1, ROW($A$1:INDEX(A:A,24)))))


Please note that the reference to $A$1 should stay in place regardless of the row. (Excel changes it if a new row is inserted as the first row.)


Cheers,

Sajan.
 
Hi Shrivallabha,


Good catch. SEARCH() was returning a false positive when it encountered ",11" when ",1" was being sought. I think the issue can be resolved by "wrapping" the source string in commas.


The revised formula is as follows (entered with Ctrl+Shift+Enter in cell C1):

=MIN(IF(ISNUMBER(SEARCH("," & ROW($A$1:INDEX(A:A,24)) & ",", "," & SUBSTITUTE(A1, " ","") & ",")), IF(ROW($A$1:INDEX(A:A,24))>=B1, ROW($A$1:INDEX(A:A,24)))))


Let me know if you see any other conditions that are not being handled correctly.


Regards,

Sajan.
 
You must log in or register to reply here.
Back
Top