Microsoft corporation Excel 2013 32/64-b...

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

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 functionThis wouldn't be homework by any chance?

MIKE

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.

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

For your question, see how this works for you: =MATCH(CEILING(B1,10),A1:A8,0)

MIKE

message edited by mmcconaghy

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

constrainingyourself to just INDEX and MATCH.

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

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.

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 950The 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) = 25Step II

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

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

Ask Your Question

Weekly Poll