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

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!

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

Don wrote:
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

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

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!

"Scoops" wrote:


Don wrote:
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

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

Try...

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

Hope this helps!

In article ,
Don wrote:

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!

"Scoops" wrote:


Don wrote:
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

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

Hi!

Try this:

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

=MIN(IF(A1:A710,A1:A7))

Biff

"Don" wrote in message
...
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!

"Scoops" wrote:


Don wrote:
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

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

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

Biff

"Domenic" wrote in message
...
Try...

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

Hope this helps!

In article ,
Don wrote:

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!

"Scoops" wrote:


Don wrote:
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

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

P.S.

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

Biff

"Biff" wrote in message
...
If there is an exact match that formula returns the exact match, not the
next higher value.

Biff

"Domenic" wrote in message
...
Try...

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

Hope this helps!

In article ,
Don wrote:

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!

"Scoops" wrote:


Don wrote:
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

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

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" wrote:

Try...

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

Hope this helps!

In article ,
Don wrote:

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!

"Scoops" wrote:


Don wrote:
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

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

In article ,
"Biff" wrote:

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.

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

From the OP's response, it looks like [i] assumed correctly. :)

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

Biff

"Domenic" wrote in message
...
In article ,
"Biff" wrote:

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.

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

In article ,
"Biff" wrote:

From the OP's response, it looks like [i] assumed correctly. :)

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

Biff

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