Expanding a function

Hello, unfortunately I can't attach an example at the moment so I wil try to explain my request
I have a spreadsheet with 20k lines (numbers only), which start on line 6 and I copy paste any one of those lines onto line 5. My purpose is to find matching information on any other lines in order to match line 5. On line 2, row F I have inserted the function : =F$5*0.25
On line 3, row F I have another function : =F$5-F$2
On line 4, row F I have another function : =F$5+F$2
So basically I am trying to find any number from the 20k lines to be within a 25% range of my number on line 5.
My problem is as follows;
If the number on line 5 is big (example 100) then my range is big too, whereas if the number on line 5 (example 10) is small then my range is small too which is not helping me.
Is there a way to expand my function on line 2, row F to do something like;
If my number on line 5, row F is between the range of 0-5 then the percentage is 50% and if the number is between range 6-10 then the percentage is 40% and if the number is between range 11-15 the percentage is 30% etc
Thanks for any info.
John

Yes unfortunately I cannot submit an example. Is it possible necessary for this particular query?
Thanks again

Ok, maybe someone else can have a look
Ps; the lines of info are not that important. If you just type in the 3 functions you will understand what I'm trying to achieve. One of the functions needs a tweak though. Thanks

Originally Posted by John Sio

Is there a way to expand my function on line 2, row F to do something like;
If my number on line 5, row F is between the range of 0-5 then the percentage is 50% and if the number is between range 6-10 then the percentage is 40% and if the number is between range 11-15 the percentage is 30%

I am only basing my response on what is quoted above.

Try something along the lines of this:

F2 =LOOKUP(F5,{0,6,11},{0.5,0.4,0.3})

No, that will return the value 0.5 if F5 is within 0-5 range
Need to read all info to understand what I need
Thanks anyway

Originally Posted by John Sio

If my number on line 5, row F is between the range of 0-5 then the percentage is 50%

Originally Posted by John Sio

No, that will return the value 0.5 if F5 is within 0-5 range

0.5 = 50%

Simply format the cell as a percent.

I need the value returned in F2 to be = to 50% of 10 for example (ie 5), so that that value can be used as part of a range in functions F3 and F4

That's fantastic, works beautiful! Thanks for your help Mr Falcon. Just quickly, would it also work with negative values in F5?

You're welcome. Glad to help.

You would have to edit the lookup ranges to account for a negative number in F5.

For example, let's say that you want the following ranges:
-100 to -60 = 20%
-60 to -20 = 40%
-20 to 0 = 60%
0 to 20 = 80%
20 to 60 = 85%
60 to 100 = 90%

You use the lower value of each range in the formula.
In this hypothetical case, the vectors in your formula would become {-100,-60,-20,0,20,60} for the lookup vector and {0.2,0.4,0.6,0.8,0.85,0.9} for the result vector.

Ok understood! Thanks again for your very descriptive information which made me understand the whole concept. Cheers

You're welcome. Happy to help!

If that solved your question, please mark this thread as SOLVED.