Looking for Assistance with Formula (Top 30 Values Closest to ZERO) Please :)

[Iccreamann21]

[TABLE="class: grid, width: 100, align: center"]
<tbody>[TR]
[TD]1[/TD]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl67, width: 139"]-0.14[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]top 30 values in column B1-B32 that is closest to 0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl67, width: 139"]-0.04[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]I want to project it as a formula in C1-C30[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl67, width: 139"]-0.02[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl67, width: 139"]0.09[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl67, width: 139"]0.23[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl67, width: 139"]-0.12[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl67, width: 139"]0.21[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl67, width: 139"]0.2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl67, width: 139"]0.04[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl67, width: 139"]0.07[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl67, width: 139"]-0.03[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl67, width: 139"]-0.07[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl67, width: 139"]-0.02[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl67, width: 139"]0.01[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl67, width: 139"]0.02[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl67, width: 139"]0.08[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl67, width: 139"]-0.15[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl67, width: 139"]0.17[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl67, width: 139"]0.33[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl67, width: 139"]-0.16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl67, width: 139"]0.02[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl67, width: 139"]0.05[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl67, width: 139"]-0.07[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl67, width: 139"]0.04[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl67, width: 139"]0.03[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl67, width: 139"]0.06[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl67, width: 139"]-0.02[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl67, width: 139"]-0.05[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl67, width: 139"]0.06[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl67, width: 139"]0.01[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl67, width: 139"]-0.03[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[tyija1995]

Hi,

Try in cell C2:
=SMALL(ABS($B$2:$B$32),ROW()-1)

Ctrl+Shift+Entered

Drag that down the C column.

 

[Iccreamann21]

[TABLE="width: 278"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]-0.14[/TD]
[TD]0.01[/TD]
[/TR]
[TR]
[TD]-0.04[/TD]
[TD]0.02[/TD]
[/TR]
[TR]
[TD]-0.02[/TD]
[TD]0.02[/TD]
[/TR]
[TR]
[TD]0.09[/TD]
[TD]0.02[/TD]
[/TR]
[TR]
[TD]0.23[/TD]
[TD]0.02[/TD]
[/TR]
[TR]
[TD]-0.12[/TD]
[TD]0.02[/TD]
[/TR]
[TR]
[TD]0.21[/TD]
[TD]0.03[/TD]
[/TR]
[TR]
[TD]0.2[/TD]
[TD]0.03[/TD]
[/TR]
[TR]
[TD]0.04[/TD]
[TD]0.03[/TD]
[/TR]
[TR]
[TD]0.07[/TD]
[TD]0.04[/TD]
[/TR]
[TR]
[TD]-0.03[/TD]
[TD]0.04[/TD]
[/TR]
[TR]
[TD]-0.07[/TD]
[TD]0.04[/TD]
[/TR]
[TR]
[TD]-0.02[/TD]
[TD]0.05[/TD]
[/TR]
[TR]
[TD]0.01[/TD]
[TD]0.05[/TD]
[/TR]
[TR]
[TD]0.02[/TD]
[TD]0.06[/TD]
[/TR]
[TR]
[TD]0.08[/TD]
[TD]0.06[/TD]
[/TR]
[TR]
[TD]-0.15[/TD]
[TD]0.07[/TD]
[/TR]
[TR]
[TD]0.17[/TD]
[TD]0.07[/TD]
[/TR]
[TR]
[TD]0.33[/TD]
[TD]0.07[/TD]
[/TR]
[TR]
[TD]-0.16[/TD]
[TD]0.08[/TD]
[/TR]
[TR]
[TD]0.02[/TD]
[TD]0.09[/TD]
[/TR]
[TR]
[TD]0.05[/TD]
[TD]0.09[/TD]
[/TR]
[TR]
[TD]-0.09[/TD]
[TD]0.12[/TD]
[/TR]
[TR]
[TD]-0.07[/TD]
[TD]0.14[/TD]
[/TR]
[TR]
[TD]0.04[/TD]
[TD]0.15[/TD]
[/TR]
[TR]
[TD]0.03[/TD]
[TD]0.16[/TD]
[/TR]
[TR]
[TD]0.06[/TD]
[TD]0.17[/TD]
[/TR]
[TR]
[TD]-0.02[/TD]
[TD]0.2[/TD]
[/TR]
[TR]
[TD]-0.05[/TD]
[TD]0.21[/TD]
[/TR]
[TR]
[TD]0.06[/TD]
[TD]0.23[/TD]
[/TR]
[TR]
[TD]0.01[/TD]
[TD]0.33[/TD]
[/TR]
[TR]
[TD]-0.03[/TD]
[TD]#NUM![/TD]
[/TR]
</tbody>[/TABLE]

I tried the suggested above and this was my results. I would need it to be the closest to 0, so like 0, .1, -.1, .2, -.2 ....ect ect

 

[RossTattersall]

It may or may not matter, but this will return the absolute value of the number, so if the second closest to 0 is -0.1, it would return 0.1 in row 2.

EDIT: Looks like you noticed yourself!

 

[tyija1995]

Aha,

Good spot guys. Try the same formula and multiply by SIGN of the value.

SMALL(ABS($B$2:$B$32),ROW()-1)*SIGN($B2)

 

[Iccreamann21]

When I try {=SMALL(ABS($B$3:$B$34),ROW()-1)*SIGN($B3)}

I receive #NUM ! message.

 

[tyija1995]

Did you Ctrl+Shift+Enter (CSE) the formula?

EDIT: If you're starting formula on row 3 use ROW()-2 instead of ROW()-1

 

[Iccreamann21]

Ok, I think I am getting closer Here are a few things listed below that I noticed. 0.05 was changed to a -0.05, 0.14 was changed to -0.14 and the random 0 at the bottom.


First Column are Values

Second Column is formulas
{=SMALL(ABS($D$3:$D$34),ROW()-2)*SIGN($D3)}


[TABLE="width: 304"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]-0.09[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]0.07[/TD]
[TD]0.01[/TD]
[/TR]
[TR]
[TD]0.05[/TD]
[TD]0.01[/TD]
[/TR]
[TR]
[TD]-0.1[/TD]
[TD]-0.01[/TD]
[/TR]
[TR]
[TD]0.15[/TD]
[TD]0.02[/TD]
[/TR]
[TR]
[TD]-0.01[/TD]
[TD]-0.04[/TD]
[/TR]
[TR]
[TD]0.1[/TD]
[TD]0.04[/TD]
[/TR]
[TR]
[TD]0.07[/TD]
[TD]0.04[/TD]
[/TR]
[TR]
[TD]-0.04[/TD]
[TD]-0.04[/TD]
[/TR]
[TR]
[TD]0.14[/TD]
[TD]0.04[/TD]
[/TR]
[TR]
[TD]0.08[/TD]
[TD]0.04[/TD]
[/TR]
[TR]
[TD]-0.07[/TD]
[TD]-0.05[/TD]
[/TR]
[TR]
[TD]-0.02[/TD]
[TD]-0.05[/TD]
[/TR]
[TR]
[TD]-0.07[/TD]
[TD]-0.06[/TD]
[/TR]
[TR]
[TD]0.01[/TD]
[TD]0.06[/TD]
[/TR]
[TR]
[TD]-0.05[/TD]
[TD]-0.06[/TD]
[/TR]
[TR]
[TD]0.04[/TD]
[TD]0.07[/TD]
[/TR]
[TR]
[TD]0.06[/TD]
[TD]0.07[/TD]
[/TR]
[TR]
[TD]0.15[/TD]
[TD]0.07[/TD]
[/TR]
[TR]
[TD]-0.06[/TD]
[TD]-0.07[/TD]
[/TR]
[TR]
[TD]0.09[/TD]
[TD]0.08[/TD]
[/TR]
[TR]
[TD]0.04[/TD]
[TD]0.09[/TD]
[/TR]
[TR]
[TD]-0.16[/TD]
[TD]-0.09[/TD]
[/TR]
[TR]
[TD]-0.17[/TD]
[TD]-0.1[/TD]
[/TR]
[TR]
[TD]0.06[/TD]
[TD]0.1[/TD]
[/TR]
[TR]
[TD]0.04[/TD]
[TD]0.1[/TD]
[/TR]
[TR]
[TD]-0.17[/TD]
[TD]-0.14[/TD]
[/TR]
[TR]
[TD]0.01[/TD]
[TD]0.15[/TD]
[/TR]
[TR]
[TD]-0.1[/TD]
[TD]-0.15[/TD]
[/TR]
[TR]
[TD]-0.04[/TD]
[TD]-0.16[/TD]
[/TR]
[TR]
[TD]-0.04[/TD]
[TD]-0.17[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[tyija1995]

I'm really not sure why you are getting those 2 random sign errors to be honest, the 0 at the end is because it is picking up the SIGN of 0 which is 0.

 

[Iccreamann21]

What about the 0 at the top of the column? Column B only has 1 "zero", but column C has 2 "zeros"