I have an inventory stock report that I am attempting to sort by
priority of what needs to be done daily. I am trying to avoid a double
sort of the entire worksheet by writing a formula. In the attached file
is my sample data. If possible i want to be able to sort the entire file
by the %Neg collum.

The formula for the %Neg collum is:
=IF(D120,(D12/E12),IF(D12<0,((D12/E12)/1),))


What I am attempting to accomplish is: If I2 is Negative then sort from
Highest to Lowest. If I2 is positive then sort from Lowest to Highest.

Any help is appreciated!


+-------------------------------------------------------------------+
|Filename: Sample.doc |
|Download: http://www.excelforum.com/attachment.php?postid=4410 |
+-------------------------------------------------------------------+

--
Remotruker
------------------------------------------------------------------------
Remotruker's Profile: http://www.excelforum.com/member.php...o&userid=32082
View this thread: http://www.excelforum.com/showthread...hreadid=518379

I haven't opened your file, but I assume column I is the same as %Neg
that you refer to. When you say:

If I2 is Negative then sort from Highest to Lowest.

do you mean "most negative" for Highest? i.e. is -100 "higher" than -10
? If so, can't you just sort on this column in ascending order, so you
go from -100 to -10 to +10 to +100?

Or, do you mean that you want the sort order to go -10, -100, +10, +100
?

Please clarify.

Pete

Pete_UK Wrote:
I haven't opened your file, but I assume column I is the same as %Neg
that you refer to. When you say:

If I2 is Negative then sort from Highest to Lowest.

do you mean "most negative" for Highest? i.e. is -100 "higher" than
-10
? If so, can't you just sort on this column in ascending order, so you
go from -100 to -10 to +10 to +100?

Or, do you mean that you want the sort order to go -10, -100, +10,
+100
?

Please clarify.

Pete

I'd like the sort from top to bottom

-5 , -6, -9, -100, -300, 5, 8, 15, 100, ect


--
Remotruker
------------------------------------------------------------------------
Remotruker's Profile: http://www.excelforum.com/member.php...o&userid=32082
View this thread: http://www.excelforum.com/showthread...hreadid=518379

Assuming that column I is the one you want to sort on, you can add this
formula to J2:

=IF(i2<0,MIN(i:i) - i2 - 0.00001,i2)

then copy down for as many items as you have in column I. You can now
include column J in your sort range and sort on column J. Once sorted
you can delete column J. This will give you the sort order you require,
i.e. -5, -6, -9, -100, -300, 5, 8, 15, 100 etc.

The reason for the 0.00001 is to ensure that the minimum value does not
get treated as zero.

I don't understand the formula in your opening post - you are dividing
by 1(?)

Hope this helps.

Pete

Pete_UK Wrote:
Assuming that column I is the one you want to sort on, you can add this
formula to J2:

=IF(i2<0,MIN(i:i) - i2 - 0.00001,i2)

then copy down for as many items as you have in column I. You can now
include column J in your sort range and sort on column J. Once sorted
you can delete column J. This will give you the sort order you
require,
i.e. -5, -6, -9, -100, -300, 5, 8, 15, 100 etc.

The reason for the 0.00001 is to ensure that the minimum value does
not
get treated as zero.

I don't understand the formula in your opening post - you are dividing
by 1(?)

Hope this helps.

Pete


Yes this helps so much :) . The divide by 1 was a mistake ,
i was dividing by -1 in a previous attempt.


--
Remotruker
------------------------------------------------------------------------
Remotruker's Profile: http://www.excelforum.com/member.php...o&userid=32082
View this thread: http://www.excelforum.com/showthread...hreadid=518379

Ok, thanks for feeding back.

Pete