Find closest number to zero

E

evdokimos

Board Regular

Joined

Mar 10, 2003

Messages

72

• Mar 20, 2003

• #2

Why you dont use the data/sort technique
Cheers

 

Upvote 0

Y

yesterday

New Member

Joined

Mar 5, 2003

Messages

34

• Mar 20, 2003

• #3

use the ABS() function

If column A has your values, use column B to evaluate. E.g.:
B1 =if(A1=0,"z",ABS(0-A1))
Then sort on Column B, ascending

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Mar 20, 2003

• #4

Book1
	A	B	C	D
1	6
2	-3		-1
3	5
4	-1
5	4
6	2
7	3
8	-1
9	6
10	4
11	1
12	6
13	-3
14	6
15	0
16
Sheet1

The formula used is...

=INDEX(A1:A15,MATCH(SMALL(INDEX(ABS(A1:A15),0,1),COUNTIF(A1:A15,0)+1),INDEX(ABS(A1:A15),0,1),0))

Of course the positive value 1 in the range is equally correct. The MATCH bit will pick out either -1 or 1, that's whichever occurs early in the current range of values.

 

Upvote 0

raihnman

Board Regular

Joined

Nov 5, 2002

Messages

99

• Mar 20, 2003

• #5

Thanks for everyone's help. I appreciate all the inputs.

Aladin, your code is great. Thanks for taking the time to solve my problem so eloquently.

Raihnman

 

Upvote 0

C

cdvdm

New Member

Joined

Nov 3, 2006

Messages

40

• Aug 2, 2010

• #6

This formula is almost perfect for something I am working on, but what if zero would be the best number for me to have returned? How can I update this formula (or write a new one) that would get me the closest to zero, including zero?
Thanks in advance!

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Aug 2, 2010

• #7

cdvdm said:

This formula is almost perfect for something I am working on, but what if zero would be the best number for me to have returned? How can I update this formula (or write a new one) that would get me the closest to zero, including zero?
Thanks in advance!

Click to expand...

We need to remove the CountIf bit and change Small to Min...

=INDEX(A1:A15,MATCH(MIN(INDEX(ABS(A1:A15),0,1)),INDEX(ABS(A1:A15),0,1),0))

 

Upvote 0

C

cdvdm

New Member

Joined

Nov 3, 2006

Messages

40

• Aug 2, 2010

• #8

Thank you very much!

 

Upvote 0

C

cdvdm

New Member

Joined

Nov 3, 2006

Messages

40

• Aug 2, 2010

• #9

Hi Aladin,
I found that I have some rows of data that are all negative numbers, some have all positive, and some have a mixture. How can I update this formula to give me the number closest to zero, regardless of +/-?

 

Upvote 0