Shift all values in a range until ≤ range of values in array table

Hi All,

I am wondering how I can input a range of values in a sheet and use formula to calculate what range of values within an array that the input values are all less than or equal to.

Edit - I have attached a WS as example. Row 4 should contain the formula the works out what range my inputs values are less than or equal too. In this example Range 8 is the answer.

Please note I am only looking for the closest range of values that my input range is less than or equal to.

Hope this make sense.

Hi Pete,

Thanks for reply. MAX function returns the maximum value in a range. It does not do a comparison of one value against another range of values.

I'm starting to think I might need to do a vlookup of some sort. Can you combine vlookup with IF statements?

I believe you would need to use the Solver for this one. You want to minimize the difference between input and range for each value and thereby getting the optimum range. I don't use the solver often, I'll see what I can do. SHG is good with Solver.

Okay, I added cell L3 where I put a 1 and Cell K5 where I put the formula
=SUM(C5:J5)
Then in B4 copied to the right
=INDEX(B$8:B$19,L3)
The Solver Add on needs to be enabled. Then you will find SOLVER on the Data Tab

Set Objective K5 to Min
Add Contraints
L3<=8
L3 = Integer
L3 >=1
Select a Solving Method : Evolutionary
Options> Evolutionary Tab Max Time without Improvement 2

Then "Solve"
See Attachment

Since you only have 12 ranges, here's a non-Solver Approach
In K8 copied down
=ABS(SUMPRODUCT(C8:J8-$C$3:$J$3))

Then in B4 copied right
=INDEX(B$8:B$19,MATCH(MIN($K$8:$K$19),$K$8:$K$19,0))

So in your first set of numbers
70 64 58 52 51 47 45 41
starting at range 7, Val #8 is is greater than the input so shouldn't the result for the first set be Range 6?

If I'm right then this in Column K copied down

=(SUMPRODUCT(--(C8:J8>=$C$3:$J$3)))

Thanks for reply. I have entered formulas in sheet but it returns the incorrect Range. In the attached sheet all the input values are <= Range 8 values in each individual column however the formula returns Range 9 as answer.

I have added a TRUE FALSE table to the sheet and added SUMPRODUCT to COL K. I now need the formula in B37 that looks up the first 1 value in Col K and returns Range X value.

I can then use Vlookup to fill in the rest of the values in C37:J37 from Table 1.

Any help appreciated.

Change the formula in K9 to this:

=SUMPRODUCT(--(C9:J9<C$3:J$3))

(i.e. get rid of the = sign), then copy down to K20.

Hope this helps.

Pete

EDIT: you will also have to change the array* formula in K37 to this:

=MIN(IF(K25:K36=1,ROW(K25:K36)-24))

if you want the lower table to show the correct result.

* commit using CSE, as advised previously.

Pete