• 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 next upper number from a table [SOLVED]

tazz

tazz

Member
Hello,

I need some help finding the next upper number from a table.

For example table has 2x2 data A1:B2

C1 has a number in the range of my table.

I want D1 to show the next number above C1 from my table.

Also it is possible to identify the location of the number showed in D1?

Any idea will help.


Thank you
 
1. Are you guaranteed that each number is unique within the table? ie, you won't have the value in C1 in both A2 and B1.


2. When you say next number above, you mean "larger than that number" or "physically 1 cell above"? If the latter, don't have your data starting in row 1, as that will cause problems.


Assuming the answer to #1 is yes, and the answer to #2 is the latter...

First, I'll shift the data down to row 2 so we don't get an error if we find a number in what was row 1.

Formula to return value directly above search criteria:

=INDEX(1:65536,SUMPRODUCT((A2:B3=C2)*ROW(A2:B3))-1,SUMPRODUCT((A2:B3=C2)*COLUMN(A2:B3)))


Address for cell with number shown in D1:

=ADDRESS(SUMPRODUCT((A2:B3=C2)*ROW(A2:B3))-1,SUMPRODUCT((A2:B3=C2)*COLUMN(A2:B3)),4)
 
Hello,

If you are looking to obtain the next number in numeric sequence (i.e. value in table that is larger than what is in C1), then try the following formula:

=SMALL(IF(COUNTIF(C1, "<"&data), data),1)


enter with Ctrl + Shift + Enter


To get the address of the value found, you could also try the following formula:

=ADDRESS(MIN(IF(data=D1,ROW(data))),MIN(IF(data=D1,COLUMN(data))))


enter with Ctrl + Shift + Enter


Cheers,

Sajan.
 
Hi Sajan ,


Does this not give the same result , entered as an array formula , using CTRL SHIFT ENTER ?


=SMALL(IF(data>C1, data),1)


I am merely trying to understand how you think !


The English phrasing of the above formula would be :


What is the smallest number within the data range , which is greater than the value in C1 ?


Narayan
 
Hi Narayan,

Yes indeed! I have no excuse to offer as a reason for complicating the formula!


So... what clues have you obtained to the ways of my mind?! :)


-Sajan.
 
I would ditto Narayan's last comment. The MMULT function still continues to make me scratch my head. =/
 
Hello,

To be more specific the values in the table are unique and by "next upper number" I mean next higher value than C1. Sorry for not being clear.

Thank you.
 
Hello,

The formulas are working really nice.

One more thing: it is possible to have the cell address to be split in 2 cells?

Ex: $B$2 in cell E1 as "B" in F1 and "2" in G1?

Thank you.
 
good day tazz


Looking forward to the answer to this.............but why would you want to split a cell reference???....if you split it you are not referencing a cell!!.....B what!!...2 what!!??
 
Then as Narayan and Sajan showed above, the array formula:

=SMALL(IF(A1:B2>C1,A1:B2),1)

or

=MIN(IF(A1:B2>C1,A1:B2))

will work.


The address is a little trickier, but another array formula:

=ADDRESS(MIN(IF(A1:B2=D1,ROW(A1:B2))),MIN(IF(A1:B2=D1,COLUMN(A1:B2))),4)


Edit: Just saw your new post...to split the info, the column letter is (again, array):

=TRIM(LEFT(SUBSTITUTE(ADDRESS(1,MIN(IF(A1:B2=D1,COLUMN(A1:B2))),2),"$",REPT(" ",5)),5))


Row is the simple array formula:

=MIN(IF(A1:B2>C1,ROW(A1:B2)))
 
You must log in or register to reply here.
Back
Top