[Solved] Formula to get numbers that meet ranged criteria

[hammerhead13]

Need Formula to get Numbers that meet Ranged Criteria.

Basically i need to get ranged number scores when they are with in a Range.

Example.

•     A          B         C         D         E         F         G
1     Metric     Score     4         3         2         1         0
2     0:16:42              0:16:00   0:17:00   0:18:00   0:19:00   0:20:00
3     89.29                96.00     93.00     90.00     88.00     86.00

Im trying to find what the score would be B2 & B3. Example b2 might be 3.25.

Thanks in advance!

Example sc.ods

(10.19 KiB) Downloaded 128 times

[jrkrideau]

I think you may have uploaded the wrong file.

[hammerhead13]

Its the same as shown. The cells themselves I may have mislabeled

[MrProgrammer]

A B C D E F G
1 Metric Score 4 3 2 1 0
2 0:16:42 0:16:00 0:17:00 0:18:00 0:19:00 0:20:00
3 89.29 96.00 93.00 90.00 88.00 86.00

I think you may have uploaded the wrong file.

Its the same as shown. The cells themselves I may have mislabeled.

They're not the same data. I will reply based on the attachment, not the posting. Use
in B5 =FORECAST(A5;$C$3:$G$3;C5:G5) which has value 3.3 and
in B6 =FORECAST(A6;$C$3:$G$3;C6:G6) which has value 3.7418 approximately.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[hammerhead13]

Thank you! This works perfect. the only thing i need it to do is max at 4. How can i have it be no higher than 4. Thanks again.

EDIT-

I did get this with an IF Formula but some of the Results still do come out accurate. would you happen to know what is wrong with them for Example

B12 should be under 2 like almost 2 since its near the Number but not over it.

i attached a new sheet. I have 2 sets of data and there are some discrepancies. Thank you again.

Example sc.ods

(14.2 KiB) Downloaded 108 times

[MrProgrammer]

I need to get ranged number scores when they are with in a Range.

some of the Results still do come out accurate.

What you have asked about in this topic is performing an Interpolation: One often has a number of data points, obtained by sampling or experimentation, which represent the values of a function for a limited number of values of the independent variable. It is often required to interpolate (i.e. estimate) the value of that function for an intermediate value of the independent variable.

As you will find, when you read the link, there are many ways to perform interpolation. When you posted, you did not specify how you wanted this done, so I chose the method that would be simplest for me (since I'm providing free help). The method I chose is called Linear Regression using the Least Squares approach. I used it because Calc provides a convenient function, FORECAST, which implements this method. This picture will show you why B12 has the value it does.

Your data points for row 12 are not in a straight line. This is inconvenient because interpolation on a straight line is very easy. FORECAST chooses a straight line (the one shown) using Least Squares, then finds the point of intersection with 84.95%. As you can see it's slightly less than 2½. The result from FORECAST is correct, though not what you expect.

Now, you are suggesting that the method that I chose does not meet your needs. So, you must indicate more precisely what those needs are. Read the linked article about Interpolation in my first sentence. Do you want Linear Interpolation? That is not difficult, though it requires a more complicated formula. Polynomial and Spilne interpolation are also possible, though they are much more complex. But we can do that if it is what you want.

Please try to get this right though. I will have limited patience with continually changing requirements. We've already learned that your first attempt at specifying the problem was incomplete. It was also confusing because your attachment did not match your post.

How can i have it be no higher than 4.

Here is another example of a requirement which was not stated explicitly. Depending on your data, and depending on the interpolation method you choose, it is possible for the interpolated result to lie outside the range [0,4]. Your revised attachment shows a value in A5 which is outside the range provided in C5:G5. Your original attachment did not include that case. How would I have known that situation may happen? To ensure the formula result is in this interval, use =MEDIAN(0;interpolating_function;4).

[hammerhead13]

I really do appreciate your help and am sorry if i seem all over the place. it difficult to express what im trying to do. My Goal is for the Contents of Column B to Reflect the Scores that Column A's Metrics are Given in a Range from 0-4. this is what im looking for an i guess we can disregard what i may have said earlier if it causing confusion. I did mark the 2 Results in question that were not in range in Red in the updated Sheet. All the other Data looks correct of what im looking for so it seems like we are in the right direction. Can you suggest a Better way to get these result?

Example sc.ods

(14.29 KiB) Downloaded 124 times

[MrProgrammer]

it difficult to express what im trying to do.

That doesn't help us much, does it? We are not mind readers here.

Basically i need to get ranged number scores when they are with in a Range.

My Goal is for the Contents of Column B to Reflect the Scores that Column A's Metrics are Given in a Range from 0-4.

This seems like a simple restatement of your first post except that you've specified the columns. I don't believe that you spent much time learning about interpolation with the link that I provided. Your attachment shows that you also want to assign scores for data outside the range.

I did mark the 2 Results in question that were not in range in Red in the updated Sheet. All the other Data looks correct of what im looking for so it seems like we are in the right direction. Can you suggest a Better way to get these result?

I will make one more guess (piecewise Linear Interpolation) at what you want. Try this (score in column L). Row 12 has approximate score 1.99, as you wished. Row 24 has score 4.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.