| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the closest value?
Does anyone have any suggestions on how to determine the closeest number?
Example One There is a list of number under column A 191,189,183,177,175,171,167,165,159,153,151 and there is a given number in cell B1, 154. I would like to determine the closest number, which match with the number in cell B1. On above example, it should return 153 in cell C1. Example Two There is a list of number under column A 191,189,183,177,175,171,167,165,155,153,151 and there is a given number in cell B1, 154. I would like to determine the closest number, which match with the number in cell B1. On above example, if the given number matches two numbers, which the difference is the same, then it should return 153 in cell C1. Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to determine the closest value?
Sort Column A in Ascending order then enter this in C1
==VLOOKUP(B1,A:A,1,TRUE) "Eric" wrote: Does anyone have any suggestions on how to determine the closeest number? Example One There is a list of number under column A 191,189,183,177,175,171,167,165,159,153,151 and there is a given number in cell B1, 154. I would like to determine the closest number, which match with the number in cell B1. On above example, it should return 153 in cell C1. Example Two There is a list of number under column A 191,189,183,177,175,171,167,165,155,153,151 and there is a given number in cell B1, 154. I would like to determine the closest number, which match with the number in cell B1. On above example, if the given number matches two numbers, which the difference is the same, then it should return 153 in cell C1. Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to determine the closest value?
Thank you very much for suggestions
Do you have any suggestions on how to use Index function to determine the value instead of VLookup? Thank everyone for any suggestions Eric "Sheeloo" wrote: Sort Column A in Ascending order then enter this in C1 ==VLOOKUP(B1,A:A,1,TRUE) "Eric" wrote: Does anyone have any suggestions on how to determine the closeest number? Example One There is a list of number under column A 191,189,183,177,175,171,167,165,159,153,151 and there is a given number in cell B1, 154. I would like to determine the closest number, which match with the number in cell B1. On above example, it should return 153 in cell C1. Example Two There is a list of number under column A 191,189,183,177,175,171,167,165,155,153,151 and there is a given number in cell B1, 154. I would like to determine the closest number, which match with the number in cell B1. On above example, if the given number matches two numbers, which the difference is the same, then it should return 153 in cell C1. Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to determine the closest value?
After sorting
=MATCH(B1,A:A,1) will give you the position of 153 =INDEX(A:A,MATCH(B1,A:A,1)) will give you 153... "Eric" wrote: Thank you very much for suggestions Do you have any suggestions on how to use Index function to determine the value instead of VLookup? Thank everyone for any suggestions Eric "Sheeloo" wrote: Sort Column A in Ascending order then enter this in C1 ==VLOOKUP(B1,A:A,1,TRUE) "Eric" wrote: Does anyone have any suggestions on how to determine the closeest number? Example One There is a list of number under column A 191,189,183,177,175,171,167,165,159,153,151 and there is a given number in cell B1, 154. I would like to determine the closest number, which match with the number in cell B1. On above example, it should return 153 in cell C1. Example Two There is a list of number under column A 191,189,183,177,175,171,167,165,155,153,151 and there is a given number in cell B1, 154. I would like to determine the closest number, which match with the number in cell B1. On above example, if the given number matches two numbers, which the difference is the same, then it should return 153 in cell C1. Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to determine the closest value?
Thank you very much for your suggestions
I cannot use the sorting function for my case, do you have any suggestions to use Index function alone to determine the value? Thank you very much for any suggestions Eric "Sheeloo" wrote: After sorting =MATCH(B1,A:A,1) will give you the position of 153 =INDEX(A:A,MATCH(B1,A:A,1)) will give you 153... "Eric" wrote: Thank you very much for suggestions Do you have any suggestions on how to use Index function to determine the value instead of VLookup? Thank everyone for any suggestions Eric "Sheeloo" wrote: Sort Column A in Ascending order then enter this in C1 ==VLOOKUP(B1,A:A,1,TRUE) "Eric" wrote: Does anyone have any suggestions on how to determine the closeest number? Example One There is a list of number under column A 191,189,183,177,175,171,167,165,159,153,151 and there is a given number in cell B1, 154. I would like to determine the closest number, which match with the number in cell B1. On above example, it should return 153 in cell C1. Example Two There is a list of number under column A 191,189,183,177,175,171,167,165,155,153,151 and there is a given number in cell B1, 154. I would like to determine the closest number, which match with the number in cell B1. On above example, if the given number matches two numbers, which the difference is the same, then it should return 153 in cell C1. Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to determine the closest value?
I don't think you can get the 'closest' value without sorting your data...
"Eric" wrote: Thank you very much for your suggestions I cannot use the sorting function for my case, do you have any suggestions to use Index function alone to determine the value? Thank you very much for any suggestions Eric "Sheeloo" wrote: After sorting =MATCH(B1,A:A,1) will give you the position of 153 =INDEX(A:A,MATCH(B1,A:A,1)) will give you 153... "Eric" wrote: Thank you very much for suggestions Do you have any suggestions on how to use Index function to determine the value instead of VLookup? Thank everyone for any suggestions Eric "Sheeloo" wrote: Sort Column A in Ascending order then enter this in C1 ==VLOOKUP(B1,A:A,1,TRUE) "Eric" wrote: Does anyone have any suggestions on how to determine the closeest number? Example One There is a list of number under column A 191,189,183,177,175,171,167,165,159,153,151 and there is a given number in cell B1, 154. I would like to determine the closest number, which match with the number in cell B1. On above example, it should return 153 in cell C1. Example Two There is a list of number under column A 191,189,183,177,175,171,167,165,155,153,151 and there is a given number in cell B1, 154. I would like to determine the closest number, which match with the number in cell B1. On above example, if the given number matches two numbers, which the difference is the same, then it should return 153 in cell C1. Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to determine the closest value?
This *array* formula will return the closest match ... as an answer to your
*first* question: =INDEX(A1:A11,MATCH(MIN(ABS(A1:A11-B1)),ABS(A1:A11-B1),0)) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. If ties are present (2 different numbers are equi-distant from the lookup value), the *first* value in the array will be returned. This *array* formula will return the *largest* value in the array if ties are present. =MAX(IF(ABS(A1:A11-B1)=MIN(ABS(A1:A11-B1)),A1:A11)) Since your scenario depicts 153 as the *2nd* value in a tie, and it also is *not* the largest of the tied values, I really don't have a suggestion at present for your second question. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Eric" wrote in message ... Thank you very much for your suggestions I cannot use the sorting function for my case, do you have any suggestions to use Index function alone to determine the value? Thank you very much for any suggestions Eric "Sheeloo" wrote: After sorting =MATCH(B1,A:A,1) will give you the position of 153 =INDEX(A:A,MATCH(B1,A:A,1)) will give you 153... "Eric" wrote: Thank you very much for suggestions Do you have any suggestions on how to use Index function to determine the value instead of VLookup? Thank everyone for any suggestions Eric "Sheeloo" wrote: Sort Column A in Ascending order then enter this in C1 ==VLOOKUP(B1,A:A,1,TRUE) "Eric" wrote: Does anyone have any suggestions on how to determine the closeest number? Example One There is a list of number under column A 191,189,183,177,175,171,167,165,159,153,151 and there is a given number in cell B1, 154. I would like to determine the closest number, which match with the number in cell B1. On above example, it should return 153 in cell C1. Example Two There is a list of number under column A 191,189,183,177,175,171,167,165,155,153,151 and there is a given number in cell B1, 154. I would like to determine the closest number, which match with the number in cell B1. On above example, if the given number matches two numbers, which the difference is the same, then it should return 153 in cell C1. Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to determine the closest value?
Thanks RD,
I thought this could not be done without sorting... "RagDyer" wrote: This *array* formula will return the closest match ... as an answer to your *first* question: =INDEX(A1:A11,MATCH(MIN(ABS(A1:A11-B1)),ABS(A1:A11-B1),0)) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. If ties are present (2 different numbers are equi-distant from the lookup value), the *first* value in the array will be returned. This *array* formula will return the *largest* value in the array if ties are present. =MAX(IF(ABS(A1:A11-B1)=MIN(ABS(A1:A11-B1)),A1:A11)) Since your scenario depicts 153 as the *2nd* value in a tie, and it also is *not* the largest of the tied values, I really don't have a suggestion at present for your second question. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Eric" wrote in message ... Thank you very much for your suggestions I cannot use the sorting function for my case, do you have any suggestions to use Index function alone to determine the value? Thank you very much for any suggestions Eric "Sheeloo" wrote: After sorting =MATCH(B1,A:A,1) will give you the position of 153 =INDEX(A:A,MATCH(B1,A:A,1)) will give you 153... "Eric" wrote: Thank you very much for suggestions Do you have any suggestions on how to use Index function to determine the value instead of VLookup? Thank everyone for any suggestions Eric "Sheeloo" wrote: Sort Column A in Ascending order then enter this in C1 ==VLOOKUP(B1,A:A,1,TRUE) "Eric" wrote: Does anyone have any suggestions on how to determine the closeest number? Example One There is a list of number under column A 191,189,183,177,175,171,167,165,159,153,151 and there is a given number in cell B1, 154. I would like to determine the closest number, which match with the number in cell B1. On above example, it should return 153 in cell C1. Example Two There is a list of number under column A 191,189,183,177,175,171,167,165,155,153,151 and there is a given number in cell B1, 154. I would like to determine the closest number, which match with the number in cell B1. On above example, if the given number matches two numbers, which the difference is the same, then it should return 153 in cell C1. Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to determine the closest value? | Excel Discussion (Misc queries) | |||
| Array Lookup to Find Closest Date and Next Closest Date | Excel Worksheet Functions | |||
| Closest value lookup? | Excel Worksheet Functions | |||
| vlookup-Closest value | New Users to Excel | |||
| Calculate the closest day | Excel Worksheet Functions | |||
Linear Mode
