Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to determine the value?
Does anyone have any suggestions on how to determine the closest number
without using sorting functions? 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.worksheet.functions
|
|||
|
|||
How to determine the value?
Eric,
=INDEX(A1:A11,MATCH(FALSE,B1<A1:A11,0)) or if you want to return the larger value in the event of a tie =INDEX(A1:A11,MATCH(MIN(ABS(A1:A11-B1)),ABS(A1:A11-B1),)) These are array formula which must be entered by pressing CTRL+Shift+Enter and not just Enter. If you do it correctly then Excel will put curly brackets around the formula {}. You can't type these yourself. If you edit the formula you must enter it again with CTRL+Shift+Enter. Mike "Eric" wrote: Does anyone have any suggestions on how to determine the closest number without using sorting functions? 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.worksheet.functions
|
|||
|
|||
How to determine the value?
Please don't multipost.
-- David Biddulph "Eric" wrote in message ... Does anyone have any suggestions on how to determine the closest number without using sorting functions? 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.worksheet.functions
|
|||
|
|||
How to determine the value?
David,
Agree totally with the irritations of multi-posting but I can't see another post. Where is it? Mike "David Biddulph" wrote: Please don't multipost. -- David Biddulph "Eric" wrote in message ... Does anyone have any suggestions on how to determine the closest number without using sorting functions? 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.worksheet.functions
|
|||
|
|||
How to determine the value?
On microsoft.public.excel.misc
today, after a similar on Nov 5th I think you answered the one on Nov 5th, Mike? -- David Biddulph "Mike H" wrote in message ... David, Agree totally with the irritations of multi-posting but I can't see another post. Where is it? Mike "David Biddulph" wrote: Please don't multipost. -- David Biddulph "Eric" wrote in message ... Does anyone have any suggestions on how to determine the closest number without using sorting functions? 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.worksheet.functions
|
|||
|
|||
How to determine the value?
David,
Had to find this in Forte Agent ( I normally use the web interface). I think you'll find that when the OP asked this very similar question previously in the event of a tie the higher value was to be returned, this time it's the lower value. Mike "David Biddulph" wrote: On microsoft.public.excel.misc today, after a similar on Nov 5th I think you answered the one on Nov 5th, Mike? -- David Biddulph "Mike H" wrote in message ... David, Agree totally with the irritations of multi-posting but I can't see another post. Where is it? Mike "David Biddulph" wrote: Please don't multipost. -- David Biddulph "Eric" wrote in message ... Does anyone have any suggestions on how to determine the closest number without using sorting functions? 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.worksheet.functions
|
|||
|
|||
How to determine the value?
Thank you very much for suggestions
Whatif I want to be a little bit complicated. Example Three There is a list of number under column A 193,191,185,179,177,173,169,167,161,155,153,149,14 5 and there is a given number in cell B1, 152. The difference between 153 and 152 is 1, but the difference between 152 and 149 is 3 I would like to return 153 in cell C1, because it is the closest value from 152. Example Four There is a list of number under column A 193,191,185,179,177,173,169,167,161,155,153,149,14 5 and there is a given number in cell B1, 151. The difference between 153 and 151 is 2, but the difference between 151 and 149 is 2 I would like to return 149 in cell C1, because it is the lower value from 151. Example Five There is a list of number under column A 193,191,185,179,177,173,169,167,161,155,153,149,14 5 and there is a given number in cell B1, 1. In this case, it will return #N/A in cell C1 I would like to return 145 in cell C1, because it is the lowest value from the list. Does you have any suggestions? Thank you very much for any suggestions Eric "Mike H" wrote: Eric, =INDEX(A1:A11,MATCH(FALSE,B1<A1:A11,0)) or if you want to return the larger value in the event of a tie =INDEX(A1:A11,MATCH(MIN(ABS(A1:A11-B1)),ABS(A1:A11-B1),)) These are array formula which must be entered by pressing CTRL+Shift+Enter and not just Enter. If you do it correctly then Excel will put curly brackets around the formula {}. You can't type these yourself. If you edit the formula you must enter it again with CTRL+Shift+Enter. Mike "Eric" wrote: Does anyone have any suggestions on how to determine the closest number without using sorting functions? 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.worksheet.functions
|
|||
|
|||
How to determine the value?
Hi,
I thibnk your relcuctance to sort is making lfe very hard for yourself. Give up and sort the range and then it becomes easy. If you dont want to sort the actual range then copy it to somewhere else and sort it leaving the original data intact. If you do that =INDEX(A1:A13,MATCH(MIN(ABS(A1:A13-B1)),ABS(A1:A13-B1),)) Does what you want Mike "Eric" wrote: Thank you very much for suggestions Whatif I want to be a little bit complicated. Example Three There is a list of number under column A 193,191,185,179,177,173,169,167,161,155,153,149,14 5 and there is a given number in cell B1, 152. The difference between 153 and 152 is 1, but the difference between 152 and 149 is 3 I would like to return 153 in cell C1, because it is the closest value from 152. Example Four There is a list of number under column A 193,191,185,179,177,173,169,167,161,155,153,149,14 5 and there is a given number in cell B1, 151. The difference between 153 and 151 is 2, but the difference between 151 and 149 is 2 I would like to return 149 in cell C1, because it is the lower value from 151. Example Five There is a list of number under column A 193,191,185,179,177,173,169,167,161,155,153,149,14 5 and there is a given number in cell B1, 1. In this case, it will return #N/A in cell C1 I would like to return 145 in cell C1, because it is the lowest value from the list. Does you have any suggestions? Thank you very much for any suggestions Eric "Mike H" wrote: Eric, =INDEX(A1:A11,MATCH(FALSE,B1<A1:A11,0)) or if you want to return the larger value in the event of a tie =INDEX(A1:A11,MATCH(MIN(ABS(A1:A11-B1)),ABS(A1:A11-B1),)) These are array formula which must be entered by pressing CTRL+Shift+Enter and not just Enter. If you do it correctly then Excel will put curly brackets around the formula {}. You can't type these yourself. If you edit the formula you must enter it again with CTRL+Shift+Enter. Mike "Eric" wrote: Does anyone have any suggestions on how to determine the closest number without using sorting functions? 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to determine the value?
Thank you very much for suggestions
When I type 151 in cell B1, it returns 153 instead of 149. Do you have any idea on how to solve it? Example Four There is a list of number under column A 193,191,185,179,177,173,169,167,161,155,153,149,14 5 and there is a given number in cell B1, 151. The difference between 153 and 151 is 2, but the difference between 151 and 149 is 2 I would like to return 149 in cell C1, because it is the lower value from 151. Thank you very much for any suggestions Eric "Mike H" wrote: Hi, I thibnk your relcuctance to sort is making lfe very hard for yourself. Give up and sort the range and then it becomes easy. If you dont want to sort the actual range then copy it to somewhere else and sort it leaving the original data intact. If you do that =INDEX(A1:A13,MATCH(MIN(ABS(A1:A13-B1)),ABS(A1:A13-B1),)) Does what you want Mike "Eric" wrote: Thank you very much for suggestions Whatif I want to be a little bit complicated. Example Three There is a list of number under column A 193,191,185,179,177,173,169,167,161,155,153,149,14 5 and there is a given number in cell B1, 152. The difference between 153 and 152 is 1, but the difference between 152 and 149 is 3 I would like to return 153 in cell C1, because it is the closest value from 152. Example Four There is a list of number under column A 193,191,185,179,177,173,169,167,161,155,153,149,14 5 and there is a given number in cell B1, 151. The difference between 153 and 151 is 2, but the difference between 151 and 149 is 2 I would like to return 149 in cell C1, because it is the lower value from 151. Example Five There is a list of number under column A 193,191,185,179,177,173,169,167,161,155,153,149,14 5 and there is a given number in cell B1, 1. In this case, it will return #N/A in cell C1 I would like to return 145 in cell C1, because it is the lowest value from the list. Does you have any suggestions? Thank you very much for any suggestions Eric "Mike H" wrote: Eric, =INDEX(A1:A11,MATCH(FALSE,B1<A1:A11,0)) or if you want to return the larger value in the event of a tie =INDEX(A1:A11,MATCH(MIN(ABS(A1:A11-B1)),ABS(A1:A11-B1),)) These are array formula which must be entered by pressing CTRL+Shift+Enter and not just Enter. If you do it correctly then Excel will put curly brackets around the formula {}. You can't type these yourself. If you edit the formula you must enter it again with CTRL+Shift+Enter. Mike "Eric" wrote: Does anyone have any suggestions on how to determine the closest number without using sorting functions? 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 max. value? | Excel Worksheet Functions | |||
How to determine the max. value? | Excel Worksheet Functions | |||
How to determine the value? | Excel Discussion (Misc queries) | |||
How to determine the value? | Excel Discussion (Misc queries) | |||
How to Determine 1st, 2nd & 3rd for a PWD | Excel Discussion (Misc queries) |