Spiky,

Thank You for your time... What I have is a punch press that will allow me
to punch material at 1/2" increments from 18" down thru 3".

I am trying to have two columns on a cutsheet that will tell my employees
how often and where to start punching the material.

The first column or solution tells them to punch at (????)on center based on
user input which is the O17 cell that I referenced.

What I want excel to do is pick the best case scenario with the user input
and the restrictions set previously 1.25 and 2.5

If the length was 96 and the user input was 14.5. The floor number for 96
and 14.5 is 87 which does not fit in the 1.25 and 2.5 parameters. so I want
excel to pick 13 from the table which has a floor number of 91 and that would
fall within the parameters with the 2.5.

I need to put holes symmetrically across the material for aesthetics.

The second column or solution tells them the measurement from the end of the
material at which to start the punching. For instance with the above example
I give you here the math problem looks like this


=(96-91)/2=2.5

or (material length-best floor solution)/2

I'm sure I have confused you as much as I have confused myself or hopefully
not. Because my wife tells me I look like an alien from slamming my head off
the desk so many times.


Thank you again,

scottgorilla


"Spiky" wrote:

What I see is the Match issue as I mentioned before. It can only work
on one column or row at a time. Then your Index is looking to the
first column, which doesn't sound like what you want. So maybe:
=INDEX(Q16:AA26,MATCH(O15,Q16:Q26,0),2)

But I'm not sure exactly what you are trying to do. For instance, the
one formula you wanted, this: "11.5 =(62-57.5)/2 which equals 2.25"
with the limits you mentioned, could be created by:
=MAX(MIN(CEILING((Q16-FLOOR(Q16,O15))/2,.5),2.5),1.5)
assuming O15 was 11.5 at the moment. But I don't know if that's what
you mean or want since I don't see how that relates to the INDEX/MATCH
formula. And I may have misunderstood your .5 increment requirement.