• 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.

Finding a closest match of a number

nagovind

nagovind

Member
Dear all

I have a range of number in the cells A1, A2...A6 (Say 1800, 300, 60, 30, 10, 2)


Then i have a Cell which i will have a RESULT say B1 (Say 78.5)


Now the query is from the resultant cell B1 = 78.5 i have to find/ locate from the range of cell A1, A2...A6 that which number in the range should be closer to that.


Here for the given value the cell is A3 and the value is 60 this is the answer i should get


How to write a formula in a separate cell


Please help
 
This is an array formula which must be confirmed using Ctrl+Shift+Enter, not just Enter:


=INDEX(A:A,MIN(IF(MIN(ABS(A1:A6-B1))=ABS(A1:A6-B1),ROW(A1:A6))))


Note that the first part of formula needs to reference the entire column that contains cells of interest, all other parts simply reference only the cells of interest.
 
Dear Mr Luke M


Thanks for your solutions it is working as per my query. But i'm unable to utilize in my application


Actually i have not asked the query as per my application sorry


The attached file shows the requirement very clearly please guide / help


http://rapidshare.com/files/444323906/Test_-_govind.xls
 
I'm afraid I can't access shared files from this computer. =(

Maybe Hui or someone else will be able to help...
 
@Nagovind

In your cells I11:I16 where are your values coming from

=IF(OR(ISERROR(G11),ISERROR(H11)),NA(),1800)

eg 1800, 300, 60 etc for other rows ?


Also where does 78.5 or 60 come from when you compare them ?
 
Dear Mr. Hui

Thanks for your clarification

Below is the file which is self explanatory

Please help / guide to achieve the solution


http://rapidshare.com/files/444392121/Test_-_govind_NEW.xls
 
Nagovind


Firstly Chandoo.org is a very casual place, Hui is much better than Mr Hui...


Try the following:

I13: =IF(OR(ISERROR(G13),ISERROR(H13),ISERROR(I12)),NA(),IF(I25>60,I25,NA()))

I14: =IF(OR(ISERROR(G14),ISERROR(H14),ISERROR(I13)),NA(),IF(I24>30,I24,NA()))

I15: =IF(OR(ISERROR(G15),ISERROR(H15),ISERROR(I14)),NA(),IF(I23>10,I23,NA()))

I16: =IF(OR(ISERROR(G16),ISERROR(H16),ISERROR(I15)),NA(),2)


I don't know where your 60, 30, 10 & 2 values come from?
 
Hui

It is not working

For the given value it is works but if i change the result as 12 then in place of 10 the result 12 to be displayed and in the pnace of 2 the value shall be #N/A

in general the values for y axis should change dynamically as per the result @ I25 only


I25 is the deciding factor and not other cells I24 and I23
 
In I13 you said if 78.1 (I25) > 60 then put the value (78.1) else put na()

and once there is an na() in the column the remaining cells shopuld be na()


So what rules are there for I14..I16
 
Hui

I thinks i have not explained clearly


I11, I12, I13, I14, I16 always have constant value as 1800, 300, 60, 30, 10, 2 [unless otherwise it is NOT modified by I25 else it will have #N/A()[


Now i have a result produced in the cell I25 which is going to modify the I11 to I16 coloumns..


OLD I11 to I16 are the Y - axis ok..this Y-axis to be modfided depending on the value in I25..


The ultimate aim is to CUT the display BELOW the values of I25 (I25 is the result as said earlier)


So for e.g if the I25 is 12 the result in I11 to I16 shall be


1800

300

60

30

12

#N/A


So for e.g if the I25 is 75 the result in I11 to I16 shall be


1800

300

75

#N/A

#N/A

#N/A


So for e.g if the I25 is 35 the result in I11 to I16 shall be


1800

300

60

35

#N/A

#N/A


Hope i explanied my query clearly

thanks in advance
 
I12:=IF(OR(ISERROR(G12),ISERROR(H12),ISERROR(I11)),NA(),IF($I$25>300,IF($I$25<1800,$I$25,NA()),300))

I13: =IF(OR(ISERROR(G13),ISERROR(H13),ISERROR(I12)),NA(),IF(I25>60,IF(I25<300,I25,NA()),60))

I14: =IF(OR(ISERROR(G14),ISERROR(H14),ISERROR(I13)),NA(),IF(I25>30,IF(I25<60,I25,NA()),30))

I15: =IF(OR(ISERROR(G15),ISERROR(H15),ISERROR(I14)),NA(),IF(I25>10,IF(I25<30,I25,NA()),10))

I16: =IF(OR(ISERROR(G16),ISERROR(H16),ISERROR(I15)),NA(),IF(I25>2,IF(I25<10,I25,NA()),2))
 
The criteria can further be explanied as I11 to I16 colums has to ...checked

each cell has to be checked with before cell also ...


I11, I12, I13, I14, I15, I16


for I16, I16 to be checked with I25 as well as I15

for I15, I15 to be checked with I25 as well as I14

for I14, I14 to be checked with I25 as well as I13

for I13, I13 to be checked with I25 as well as I12 etc,,,,
 
Can this be done for a text field which is a combination of text and number.? For example I want to search maint5 within maint1 maint7 maint10 vaint2 vaint4.I should get the value maint7 which is closest to maint5. In excel we can assume maint5 is incell a1 and the other value are in cells b1 to b5. The alphanumeric value should match exactly. Only the numeric value should have the closest match. Which means mainz5 should not return any value but maint5 returns maint7.
 
@jits50

Hi!

Would you please start a new topic instead of writing on another user's one? It might be seen as hijacking. If think that's needed include a link to this topic.

Perhaps you'd want to read the three green sticky posts at this forums main page so as to know the guidelines that will lead to know how this community operates (introducing yourself, posting files, netiquette rules, and so on).

Regards!
 
I did that as an extension of the proble alreadysolved here instead of discussing from the beginning. But I can paste the problem in a new thread.
 
You must log in or register to reply here.
Back
Top