SMALL/LARGE Command

[JTL9161]

I am working on a golf league spreadsheet. I am using the following formula to sort the members by net score.

=SUMPRODUCT((ABS(Z3-0)>ABS($Z$3:$Z$22-0))+0)+1

The problem is that in golf a negative score should put a golfer ahead of golfer with a "0" or Even score and ahead of a golfer with a plus score.

Current List
ABC +2
DEF 0
GHI +1
XYZ -1

What I need after sorting
XYZ -1
DEF 0
GHI +1
ABC +2

Thanks
James

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E
1
2
3	ABC	2		XYZ	-1
4	DEF	0		DEF	0
5	GHI	1		GHI	1
6	XYZ	-1		ABC	2
Data

Cell Formulas
Range	Formula
D3:E6	D3	=SORT(A3:B6,2)
Dynamic array formulas.

 

[JTL9161]

So sorry. I was talking about the standing number which I did not include in the example. The problem is the SUMPRODUCT formula I am using is not putting the sort order correct.

Current List
1 ABC +2
3 DEF 0
2 GHI +1
4 XYZ -1

What I need after sorting
1 XYZ -1
2 DEF 0
3 GHI +1
4 ABC +2

 

[Fluff]

Can you post some sample data (using the XL2BB add-in) showing exactly what you have & what you want

 

[JTL9161]

x

 

[JTL9161]

1	ABC	+2
3	DEF	0
4	GHI	-1
2	JKL	+1
5	MNO	-2
Need to get
1	MNO	-2
2	GHI	-1
3	DEF	0
4	JKL	+1
5	ABC	+2

 

[Fluff]

Then why can't you use what I suggested?

 

[JTL9161]

The SUMPRODUCT is giving the wrong standing number based on the total.

It thinks ABC is #1 when it should be #5, MNO is #5 when it should be #1., GHI is #4 when it should be #2. Its the standing number that I am trying to get correct.

 

[Fluff]

In that case can you please show EXACTLY what you have & are trying to do, including any formulae.
When you post the data please use the mini-sheet option & not the table option

 

[JTL9161]

Hope this worked.

Test1.xlsx
	B
14	2
Sheet1