formula for a # in a column of #'s that's closest to a certain val

[Guest]

I'm looking for a function or formula in Excel that will find a number in a
range of numbers that is closest to a specified number. Example: I have a
column of 7 cells that contain cummulative percentages from 0 - 100. It will
always be theses same cells, but will have varying percent values depending
on other calculations. I need to find the first number that's closest to and
below 10. I also need to find the first number that's closest to and above
10. I need this for 3 other values (40, 50, & 90)as well, but if I can get
an example of a formula, I could probably go from there.

Thanks in advance for the help!

 

[Scoops]

I'm looking for a function or formula in Excel that will find a number in a
range of numbers that is closest to a specified number. Example: I have a
column of 7 cells that contain cummulative percentages from 0 - 100. It will
always be theses same cells, but will have varying percent values depending
on other calculations. I need to find the first number that's closest to and
below 10. I also need to find the first number that's closest to and above
10. I need this for 3 other values (40, 50, & 90)as well, but if I can get
an example of a formula, I could probably go from there.

Thanks in advance for the help!

Hi Don

Try this in your result cell:

=VLOOKUP(10,$A$1:$A$7,1,TRUE)

Copy it down three cells and amend the 10 to 40, 50 and 90
respectively.

Note that you can make the "10" a reference to another cell so that you
could change that cell rather than amending the formula in the future.

Regards

Steve

 

[Guest]

Thanks, that works great for the number closest to less than 10. I also need
the same type solution for the number closest to more than 10. For instance
a1=1.34, a2=19.13, a3=36.64, a4=42.98, a5=76.41, a6=91.22, a7=100. Your
formula only gets the first number under 10. How can I get a formula to give
me the first number above 10. Like in this case I would need it to select
the value in cell a2 (19.13). Keep in mind the values in these cells will
vary depending on other calcs in the spreadsheet. Thanks again!

 

[Domenic]

Try...

=INDEX(A1:A7,MATCH(10,A1:A7)+(LOOKUP(10,A1:A7)<>10))

Hope this helps!

 

[Biff]

Hi!

Try this:

This is an array formula and MUST be entered using the key combination of
CTRL,SHIFT,ENTER:

=MIN(IF(A1:A7>10,A1:A7))

Biff

 

[Biff]

If there is an exact match that formula returns the exact match, not the
next higher value.

Biff

 

[Biff]

P.S.

Also, if the lookup_value is less than the lowest value in the range it
returns #N/A.

Biff

 

[Guest]

Hey thanks alot!!! I knew it was probably something fairly simple for an
Excel Guru! I know just enough to get in a bind! It works
great..........thanks again to Scoops & Domenic.

 

[Domenic]

If there is an exact match that formula returns the exact match, not the
next higher value.

Since VLOOKUP returned the desired results for the first part, I assumed
that the reverse would be true for the second part. From the OP's
response, it looks like assumed correctly.

Also, if the lookup_value is less than the lowest value in the range it
returns #N/A.

Good point, Biff! Depending on what the OP is looking for, maybe some
variation of the following...

=IF(D2<>"",IF(D2>=A1,INDEX(A1:A7,MATCH(D2,A1:A7)+(LOOKUP(D2,A1:A7)<>D2)),
A1),"N/A")

....where D2 contains the lookup/target value.

 

[Biff]

From the OP's response, it looks like assumed correctly.

Yeah, I'm starting to get too nit-picky!

Biff

 

[Domenic]

From the OP's response, it looks like assumed correctly.

Yeah, I'm starting to get too nit-picky!

Biff[/QUOTE]

Well, somebody has to keep us on our toes...