Cell Referencing

[Wally]

Hi,
I need to find a value in an array that closest matches a
given value. For instance;

let's say that the following array exist

A | B |
1 3.2 100.3
2 4.6 120.5
3 5.5 136.1
4 6.3 145.2
5 7.1 166.8
6 8.7 181.4
7 9.3 200.2

I am using MATCH(6.8,A1:A7,1) This returns 4 because the
4th position holds the highest value smaller than or equal
to (6.8). So that tells me the row #. Now, I need to
find which value is closer to (6.8), (6.3) or (7.1)

Is there an easier way to do this or how do I use the
known row # (in this instance - (4) to compare the values?

e.g = If(6.8 - [value in row(4)col(1)] < [value in row
(4+1)] - 6.8, Value in row(4)col(1), row(4+1)col(1))

Hope this is not too vague.
Thanks for any help
Wally

 

[Jim Rech]

Is there an easier way to do this

If there is it isn't obvious to me.

--
Jim Rech
Excel MVP
| Hi,
| I need to find a value in an array that closest matches a
| given value. For instance;
|
| let's say that the following array exist
|
| A | B |
| 1 3.2 100.3
| 2 4.6 120.5
| 3 5.5 136.1
| 4 6.3 145.2
| 5 7.1 166.8
| 6 8.7 181.4
| 7 9.3 200.2
|
| I am using MATCH(6.8,A1:A7,1) This returns 4 because the
| 4th position holds the highest value smaller than or equal
| to (6.8). So that tells me the row #. Now, I need to
| find which value is closer to (6.8), (6.3) or (7.1)
|
| Is there an easier way to do this or how do I use the
| known row # (in this instance - (4) to compare the values?
|
| e.g = If(6.8 - [value in row(4)col(1)] < [value in row
| (4+1)] - 6.8, Value in row(4)col(1), row(4+1)col(1))
|
| Hope this is not too vague.
| Thanks for any help
| Wally
|
|
|
|

 

[Frank Kabel]

Hi
try the following array formula (entered with cTRL+SHIFT+ENTER):
= INDEX(A1:A10,MATCH(MIN(ABS(A1:A10-6.8)),ABS(A1:A10-6.8),0))

 

[Frank Kabel]

Hi
just to add: this formula does NOT require a sorted list

--
Regards
Frank Kabel
Frankfurt, Germany

Hi
try the following array formula (entered with cTRL+SHIFT+ENTER):
= INDEX(A1:A10,MATCH(MIN(ABS(A1:A10-6.8)),ABS(A1:A10-6.8),0))

Hi,
I need to find a value in an array that closest matches a
given value. For instance;

let's say that the following array exist

A | B |
1 3.2 100.3
2 4.6 120.5
3 5.5 136.1
4 6.3 145.2
5 7.1 166.8
6 8.7 181.4
7 9.3 200.2

I am using MATCH(6.8,A1:A7,1) This returns 4 because the
4th position holds the highest value smaller than or equal
to (6.8). So that tells me the row #. Now, I need to
find which value is closer to (6.8), (6.3) or (7.1)

Is there an easier way to do this or how do I use the
known row # (in this instance - (4) to compare the values?

e.g = If(6.8 - [value in row(4)col(1)] < [value in row
(4+1)] - 6.8, Value in row(4)col(1), row(4+1)col(1))

Hope this is not too vague.
Thanks for any help
Wally

 

[wally]

Thanks Frank for the reply,

First, what do you mean by (entered with
cTRL+SHIFT+ENTER):

I'm lost on that one!

Anyway, I could not get your suggestion to work. All I
get is #VALUE# error. However, from your suggestion I
figured that I can use MIN function by just creating a
field next to my values to equal =ABS(6.8-A1) then doing
a =MIN(C1:C10) then using this value to do a VLOOKUP on
original array. Anyway I think I can make it work this
way.
Thanks again for your help
Wally

-----Original Message-----
Hi
just to add: this formula does NOT require a sorted list

--
Regards
Frank Kabel
Frankfurt, Germany

Hi
try the following array formula (entered with cTRL+SHIFT+ENTER):
= INDEX(A1:A10,MATCH(MIN(ABS(A1:A10-6.8)),ABS(A1:A10- 6.8),0))

Hi,
I need to find a value in an array that closest matches a
given value. For instance;

let's say that the following array exist

A | B |
1 3.2 100.3
2 4.6 120.5
3 5.5 136.1
4 6.3 145.2
5 7.1 166.8
6 8.7 181.4
7 9.3 200.2

I am using MATCH(6.8,A1:A7,1) This returns 4 because the
4th position holds the highest value smaller than or equal
to (6.8). So that tells me the row #. Now, I need to
find which value is closer to (6.8), (6.3) or (7.1)

Is there an easier way to do this or how do I use the
known row # (in this instance - (4) to compare the values?

e.g = If(6.8 - [value in row(4)col(1)] < [value in row
(4+1)] - 6.8, Value in row(4)col(1), row(4+1)col(1))

Hope this is not too vague.
Thanks for any help
Wally

.

 

[Frank Kabel]

Hi
normally you would commit a formula entry with hitting ENTER. For array
formulas you have to hit CTRL+SHIFT+ENTER together. See:
http://www.cpearson.com/excel/array.htm

So just enter the formula and press these three keys

--
Regards
Frank Kabel
Frankfurt, Germany

Thanks Frank for the reply,

First, what do you mean by (entered with
cTRL+SHIFT+ENTER):

I'm lost on that one!

Anyway, I could not get your suggestion to work. All I
get is #VALUE# error. However, from your suggestion I
figured that I can use MIN function by just creating a
field next to my values to equal =ABS(6.8-A1) then doing
a =MIN(C1:C10) then using this value to do a VLOOKUP on
original array. Anyway I think I can make it work this
way.
Thanks again for your help
Wally

-----Original Message-----
Hi
just to add: this formula does NOT require a sorted list

--
Regards
Frank Kabel
Frankfurt, Germany

Hi
try the following array formula (entered with cTRL+SHIFT+ENTER):
= INDEX(A1:A10,MATCH(MIN(ABS(A1:A10-6.8)),ABS(A1:A10- 6.8),0))




Wally wrote:
Hi,
I need to find a value in an array that closest matches a
given value. For instance;

let's say that the following array exist

A | B |
1 3.2 100.3
2 4.6 120.5
3 5.5 136.1
4 6.3 145.2
5 7.1 166.8
6 8.7 181.4
7 9.3 200.2

I am using MATCH(6.8,A1:A7,1) This returns 4 because the
4th position holds the highest value smaller than or equal
to (6.8). So that tells me the row #. Now, I need to
find which value is closer to (6.8), (6.3) or (7.1)

Is there an easier way to do this or how do I use the
known row # (in this instance - (4) to compare the values?

e.g = If(6.8 - [value in row(4)col(1)] < [value in row
(4+1)] - 6.8, Value in row(4)col(1), row(4+1)col(1))

Hope this is not too vague.
Thanks for any help
Wally

.

 

[wally]

OK Frank, you are the man.

Here is the actual formula that finally worked.

=INDEX(A9:A128,MATCH(MIN(ABS(B9:B128-B4)),ABS(B9:B128-
B4),0),0)

A9:A128 holds the actual number that I need to get.
Your MATCH Function was right on.
It appears that I just needed an additional ,0) on the end
and of course the Ctrl+Shift+Enter.

Thank you so much,
I learned alot here and it made me think too.
Wally

-----Original Message-----
Hi
normally you would commit a formula entry with hitting ENTER. For array
formulas you have to hit CTRL+SHIFT+ENTER together. See:
http://www.cpearson.com/excel/array.htm

So just enter the formula and press these three keys

--
Regards
Frank Kabel
Frankfurt, Germany

Thanks Frank for the reply,

First, what do you mean by (entered with
cTRL+SHIFT+ENTER):

I'm lost on that one!

Anyway, I could not get your suggestion to work. All I
get is #VALUE# error. However, from your suggestion I
figured that I can use MIN function by just creating a
field next to my values to equal =ABS(6.8-A1) then doing
a =MIN(C1:C10) then using this value to do a VLOOKUP on
original array. Anyway I think I can make it work this
way.
Thanks again for your help
Wally

-----Original Message-----
Hi
just to add: this formula does NOT require a sorted list

--
Regards
Frank Kabel
Frankfurt, Germany


Frank Kabel wrote:
Hi
try the following array formula (entered with cTRL+SHIFT+ENTER):
= INDEX(A1:A10,MATCH(MIN(ABS(A1:A10-6.8)),ABS

(A1:A10-
6.8),0))

Wally wrote:
Hi,
I need to find a value in an array that closest matches a
given value. For instance;

let's say that the following array exist

A | B |
1 3.2 100.3
2 4.6 120.5
3 5.5 136.1
4 6.3 145.2
5 7.1 166.8
6 8.7 181.4
7 9.3 200.2

I am using MATCH(6.8,A1:A7,1) This returns 4

because
the

4th position holds the highest value smaller than or equal
to (6.8). So that tells me the row #. Now, I need to
find which value is closer to (6.8), (6.3) or (7.1)

Is there an easier way to do this or how do I use the
known row # (in this instance - (4) to compare the values?

e.g = If(6.8 - [value in row(4)col(1)] < [value in row
(4+1)] - 6.8, Value in row(4)col(1), row(4+1)col(1))

Hope this is not too vague.
Thanks for any help
Wally
.

.