Greater than feature of Match function with sorting data

Microsoft corporation Excel 2013 32/64-b...
January 4, 2017 at 15:14:42
Specs: Microsoft Excel
I have data in column A starting from A1 to A8 as 20,30,40,50,60,70,80,90. Now I can enter a number in B1 which can be any number from 20 to 90 with incremental value of 5(interpolation), e.g., I can enter 25 or 40 or 65 or 90. So there are 15 possible input.
Now, I want to write a formula in cell C1 to get the one plus position of the entered number. For example, if I enter 25, then I should get output as 2, but not as 1, which will be the ideal output if we use match function with 1 as third argument. Also, if I enter 30 then also it should give me output as 2.
This can be easily done using below formula:
{=MATCH(B1,LARGE(A1:A8,ROW(INDIRECT("1:8"))),-1)}

However, there are some constraint as mentioned below:
1. You can only use INDEX and MATCH function
2. Data cannot be changed to solve this problem. You cannot add a row or a column. Neither can you sort or filter data.

message edited by ArjunBadhan


See More: Greater than feature of Match function with sorting data

Reply ↓  Report •


#1
January 4, 2017 at 16:30:55
In your example formula,

{=MATCH(B1,LARGE(A1:A8,ROW(INDIRECT("1:8"))),-1)}

You have used MATCH, LARGE, ROW & INDIRECT yet you say:

However, there are some constraint as mentioned below:
1. You can only use INDEX and MATCH function

This wouldn't be homework by any chance?

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#2
January 4, 2017 at 16:54:55
Boy, if this doesn't sound like a homework problem I don't know what does! We are not in the habit of doing people's homework for them.

In any case, the formula that you say "easily" returns 2 for an input of 25 or 30 actually returns 7 when I try it.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#3
January 4, 2017 at 18:37:29
Hi guys, Hope you are doing good and hope you guys remember me.

This is not a homework problem. I am 31 years old and not a good age to go to school. Actually, I have a habit of trying problems online/youtube to sharpen my excel skills and I came across this problem on https://www.scribd.com/. Link to the problem is: https://www.scribd.com/document/325...

Derby, I apologize I pasted the wrong formula. I was trying to say that we can use normal match function with 1 in 3rd argument to get the less than number but I do not want to have the less than but greater than number in this case. As I mentioned if we enter 25 or 30, it should return 2. So, what I tried was =MATCH(B1,A1:A8,1) and =MATCH(B1,A1:A8,1)+1, but didnt get what I was looking for

Mike, I was trying to find out the ways to get this done and thought to give it a shot without constraints, so i trying MATCH,LARGE,INDIRECT AND ROW, but with constraints, I was not able to do and then I came here to take assistance from you guys.

message edited by ArjunBadhan


Reply ↓  Report •

Related Solutions

#4
January 4, 2017 at 20:45:23
For your question, see how this works for you:

=MATCH(CEILING(B1,10),A1:A8,0)

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#5
January 5, 2017 at 05:49:51
The link to the problem that you provided appears to require a download or an app installation. That is not typically something I like to do just to answer a question in this forum.

I also don't understand why you are putting constraints on the solution. Why not simply use what works?

I can understand trying to find the most efficient solution by using as few functions as possible, but I don't quite see the logic of constraining yourself to just INDEX and MATCH.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#6
January 5, 2017 at 10:02:01
Mike, thank you so much for the formula, it gets us closer to what we are looking here using CEILING function. However, I still looking for something where we only use INDEX and MATCH function.

Reply ↓  Report •

#7
January 5, 2017 at 10:04:57
Derby, here is the problem for you.

	
        200	400	600	800	1000
20	10	20	160	210	260
30	40	60	190	240	290
40	130	180	230	280	330
50	180	230	280	330	380
60	240	290	340	390	440
70	310	360	410	460	510
80	390	440	490	540	590
90	750	800	850	900	950

The above table gives the wind pressure for the parameters - height of the structure and the span of the structure.
Write a formula in cell L21 to find out the wind pressure for any height and span by interpolation. The user may enter any height between 20 and 90 in L20, and any span between 200 and 1000 in M20.
You cannot use any function other than Match and Index.
Data cannot be changed to solve this problem. You cannot add a row or a column. Neither can you sort or filter data.
For example, if an user gives a height of 25 and a span of 300, the wind pressure is calculated as
Step I
10 + (40 - 10) * (25 - 20)/(30 - 20) = 25

Step II
20 + (60-20) * (25 - 20)/(30 - 20) = 40

Step III
25 + (40 - 25) * (300 - 200)/(400 - 200) = 32.5


Reply ↓  Report •


Ask Question