Lookup best case based on varying input????

[scottgorilla]

I have created a table that will give me the FLOOR value of number that
constantly varies.

For example if the number is 62 and the formula for the for the floor is
=FLOOR(62,18) the solution is 54. My table starts at 18 as the significance
and descends by .5 down to 3. What I want to do is create a formula that will
pick and put the significance in one cell based on a criteria that is user
entered in another cell 12 (being the significance) or less and the
difference between the result from that column (in the table) divided by 2 in
the next column. Like this perhaps. I have a possible of 50 rows (same
column) which will likely all have several different numbers.

Column A Column B
11.5 =(62-57.5)/2 which equals 2.25

My biggest problems are:


1) my table is limited to the ability of .5 increments.

2) my result from column B can't be less than 1.25 or greater than 2.5

This gives me a very narrow window to work with but it is a must.

I tried this on a smaller version but I get #n/a as the result. O15 is the
significance.
=INDEX(Q16:AA26,MATCH(O15,Q16:AA26,0),1)




PLEASE HELP ME IM LOST


Thank You,


scottgorilla

 

[Spiky]

I have created a table that will give me the FLOOR value of number that
constantly varies.

For example if the number is 62 and the formula for the for the floor is
=FLOOR(62,18) the solution is 54. My table starts at 18 as the significance
and descends by .5 down to 3. What I want to do is create a formula that will
pick and put the significance in one cell based on a criteria that is user
entered in another cell 12 (being the significance) or less and the
difference between the result from that column (in the table) divided by 2 in
the next column. Like this perhaps. I have a possible of 50 rows (same
column) which will likely all have several different numbers.

Column A Column B
11.5 =(62-57.5)/2 which equals 2.25

My biggest problems are:

1) my table is limited to the ability of .5 increments.

2) my result from column B can't be less than 1.25 or greater than 2.5

This gives me a very narrow window to work with but it is a must.

I tried this on a smaller version but I get #n/a as the result. O15 is the
significance.
=INDEX(Q16:AA26,MATCH(O15,Q16:AA26,0),1)

PLEASE HELP ME IM LOST

Thank You,

scottgorilla

Change your Match to one column. Q16:Q26
But I don't see how your formula matches to your discussion. Is column
A in your example really column Q in your formula?

 

[scottgorilla]

Spiky,

The problem is this the table is set up where all of the reult are on the
same row. I will try the set up the table the other way and see what happens.
Yes the actual data lies within the cells in my formula and the example are
the two results I need to pull from the table.

Thanks,

scottgorilla

 

[Spiky]

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.

 

[scottgorilla]

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

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.

 

[scottgorilla]

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

 

[scottgorilla]

Spiky,

Another side note I tried both of the formulas that you gave me and the
first one will give me result using that significance but what I am looking
for is the closest significance to the input number that will give me a
result that falls somewhere equal to or between 5 and 2.5 which is then
divided by 2 and gives me the result for my second column.

The second formula gave me a result of 1.5 but the actual result for the one
I ran was .5 and returned 1.5 because that was the min but if I began the
process at 1.5 and went every 11 to the end I would have 6.5 at the opposite
end which does not give me symmetry.


Thank You again for your time.

 

[Spiky]

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.

Then maybe you need another column with an IF formula. Take out the
restrictions (MIN & MAX) from the first formula so you get all answers
shown. Then in a 2nd column, do something like:
IF(P16>2.5,xxx,IF(P16<1.25,yyy,"Floor as input is ok")).
The "xxx" & "yyy" will need to be the INDEX/MATCH to your table to get
1.25 or 2.5 as needed.

 

[scottgorilla]

Spiky,

The harder I work at this the more lost I'm becoming. Perhaps I don't need
an index/match formula at all I just tried that as I searched through these
threads looking for my answer. Your help has been outstanding but perahps I
could email you the sample sheets so you may be able to better decipher what
I'm trying desperatly to accomplish.


Thanks,


Scottgorilla

 

[Spiky]

That's fine about the email. I can't promise I'll have time before the
weekend. It's a busy week.

 

[scottgorilla]

Sent it today...