first number (in a list) larger than a certain value

[palaeontology]

I have a list in the range ... CM37:CW37

I have a value in ... BY37

I'd like to find the first number in the list range that is larger than the value in BY37.

eg: List Range in CM37:CW37 .... [TABLE="width: 704"]
<tbody>[TR]
[TD="class: xl66, width: 64"]199.0[/TD]
[TD="class: xl67, width: 64"]209[/TD]
[TD="class: xl67, width: 64"]219[/TD]
[TD="class: xl67, width: 64"]229[/TD]
[TD="class: xl67, width: 64"]239[/TD]
[TD="class: xl67, width: 64"]249[/TD]
[TD="class: xl67, width: 64"]259[/TD]
[TD="class: xl67, width: 64"]269[/TD]
[TD="class: xl67, width: 64"]279[/TD]
[TD="class: xl67, width: 64"]289[/TD]
[TD="class: xl67, width: 64"]299

[/TD]
[/TR]
</tbody>[/TABLE]

Value in BY37 ...

247.9

so the first value in the list range that is larger than 247.9 is the value 249

I've tried a few combinations of match etc, but each falls short of what i need.

Any ideas ?

Kind regards,

Chris
[TABLE="width: 704"]
<tbody>[TR]
[TD="class: xl64, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

I am sure someone will come up with a better formula than this, but this array-entered** formula is the one that came to me as I read your question...

=INDEX(37:37,1,MIN(IF(BY37<CM37:CW37,COLUMN(CM37:CW37))))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

 

[Tetra201]

Maybe

=LOOKUP(BY37,CM37:CV37,CN37:CW37)

 

[palaeontology]

Thankyou, so much, to both of you, they both worked brilliantly.

I've gone with Tetra's formula, but am happy to switch if my next query requires me to ...

I still have that list range in CM37:CW37.

I now have a value in BY37 and another value in CH37.

Is there a way to return (into the cells between BY37 and CH37) the numbers in the list range ... CM37:CW37 ... that are larger than the value in BY37 and smaller than the value in CH37 ?

I've left space for up to 8 numbers (ie : to go into BZ37 through to CG37) but there might be only one or two numbers that meet the criteria ... all depending on which student data I'm looking at.

For example ...

eg: List Range in CM37:CW37 ....[TABLE="class: cms_table, width: 704"]
<tbody>[TR]
[TD="class: cms_table_xl66, width: 64"]199.0[/TD]
[TD="class: cms_table_xl67, width: 64"]209[/TD]
[TD="class: cms_table_xl67, width: 64"]219[/TD]
[TD="class: cms_table_xl67, width: 64"]229[/TD]
[TD="class: cms_table_xl67, width: 64"]239[/TD]
[TD="class: cms_table_xl67, width: 64"]249[/TD]
[TD="class: cms_table_xl67, width: 64"]259[/TD]
[TD="class: cms_table_xl67, width: 64"]269[/TD]
[TD="class: cms_table_xl67, width: 64"]279[/TD]
[TD="class: cms_table_xl67, width: 64"]289[/TD]
[TD="class: cms_table_xl67, width: 64"]299
[/TD]
[/TR]
</tbody>[/TABLE]


Value in BY37 ...

247.9

Value in CH37 ...

266.8

So, 249 would go into BZ37, and 259 would go into CA37

Any help would be greatly welcomed,

Very kind regards,

Chris

 

[DanteAmor]

I have a list in the range ... CM37:CW37

I have a value in ... BY37

I'd like to find the first number in the list range that is larger than the value in BY37.

eg: List Range in CM37:CW37 .... [TABLE="width: 704"]
<tbody>[TR]
[TD="class: xl66, width: 64"]199.0[/TD]
[TD="class: xl67, width: 64"]209[/TD]
[TD="class: xl67, width: 64"]219[/TD]
[TD="class: xl67, width: 64"]229[/TD]
[TD="class: xl67, width: 64"]239[/TD]
[TD="class: xl67, width: 64"]249[/TD]
[TD="class: xl67, width: 64"]259[/TD]
[TD="class: xl67, width: 64"]269[/TD]
[TD="class: xl67, width: 64"]279[/TD]
[TD="class: xl67, width: 64"]289[/TD]
[TD="class: xl67, width: 64"]299
[/TD]
[/TR]
</tbody>[/TABLE]

Value in BY37 ...

247.9

so the first value in the list range that is larger than 247.9 is the value 249

I've tried a few combinations of match etc, but each falls short of what i need.

Any ideas ?

Kind regards,

Chris

If the numbers are not ordered from least to greatest, it seems to me that the formulas presented have a problem.
I present another approach, it is also a matrix formula, but if the numbers are in order or not, it shows you the correct result.


{=SUMPRODUCT(SMALL(IF(CM37:CW37>BY37,CM37:CW37),1))}


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:50.38px;" /><col style="width:50.38px;" /><col style="width:50.38px;" /><col style="width:50.38px;" /><col style="width:50.38px;" /><col style="width:50.38px;" /><col style="width:50.38px;" /><col style="width:50.38px;" /><col style="width:50.38px;" /><col style="width:50.38px;" /><col style="width:50.38px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >BX</td><td >BY</td><td >CM</td><td >CN</td><td >CO</td><td >CP</td><td >CQ</td><td >CR</td><td >CS</td><td >CT</td><td >CU</td><td >CV</td><td >CW</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >37</td><td > </td><td style="text-align:right; ">247.9</td><td style="text-align:right; ">400</td><td style="text-align:right; ">240</td><td style="text-align:right; ">260</td><td style="background-color:#ffff00; text-align:right; ">255</td><td style="text-align:right; ">245</td><td style="text-align:right; ">246</td><td style="text-align:right; ">247</td><td style="text-align:right; ">245</td><td style="text-align:right; ">246</td><td style="text-align:right; ">247</td><td style="text-align:right; ">240</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >38</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >39</td><td style="text-align:right; ">240</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >40</td><td style="text-align:right; ">400</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >41</td><td style="background-color:#ffff00; text-align:right; ">255</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >BX39</td><td >=LOOKUP(BY37,CM37:CV37,CN37:CW37)</td></tr><tr><td >BX40</td><td >{=INDEX(37:37,1,MIN(IF(BY37<CM37:CW37,COLUMN(CM37:CW37))))}</td></tr><tr><td >BX41</td><td >{=SUMPRODUCT(SMALL(IF(CM37:CW37>BY37,CM37:CW37),1))}</td></tr></table></td></tr></table>


Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

 

[Tetra201]

... Is there a way to return (into the cells between BY37 and CH37) the numbers in the list range ... CM37:CW37 ... that are larger than the value in BY37 and smaller than the value in CH37 ?..

Try the following steps:
- select range BZ37:CG37 starting from cell BZ37
- go to the Formula Bar and paste there the following formula <ch37),cm37:cw37),{1,2,3,4,5,6,7,8}),"")
=IFERROR(SMALL(IF((CM37:CW37 > BY37)*(CM37:CW37 < CH37),CM37:CW37),{1,2,3,4,5,6,7,8}),"")
- while in the Formula Bar, press Ctrl+Shift+Enter</ch37),cm37:cw37),{1,2,3,4,5,6,7,8}),"")

 

[DanteAmor]

Is there a way to return (into the cells between BY37 and CH37) the numbers in the list range ... CM37:CW37 ... that are larger than the value in BY37 and smaller than the value in CH37 ?

Reviewing the formula array of post # 5, the SumProduct function is not necessary, then it can look like this:

{=SMALL(IF(CM37:CW37>BY37,CM37:CW37),1)}

This same formula can be used for your new request, just add the condition <CH37, it would be the following array formula :

{=IFERROR(SMALL(IF(($CM$37:$CW$37 > $BY$37)*($CM$37:$CW$37 < $CH$37),$CM$37:$CW$37),COLUMNS($BY36:BY36)),"")}

Put the formula in BZ37, remember, to accept the formula array you must press Shift + Control + Enter
Then drag the formula to CG37