software

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(D12gt;0,(D12/E12),IF(D12lt;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: www.excelforum.com/attachment.php?postid=4410 |
-------------------------------------------------------------------

--
Remotruker
------------------------------------------------------------------------
Remotruker's Profile: www.excelforum.com/member.php...oamp;userid=32082
View this thread: www.excelforum.com/showthread...hreadid=518379I haven't opened your file, but I assume column I is the same as %Neg
that you refer to. When you say:

gt; If I2 is Negative then sort from Highest to Lowest.

do you mean quot;most negativequot; for Highest? i.e. is -100 quot;higherquot; 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:
gt; I haven't opened your file, but I assume column I is the same as %Neg
gt; that you refer to. When you say:
gt;
gt; gt; If I2 is Negative then sort from Highest to Lowest.
gt;
gt; do you mean quot;most negativequot; for Highest? i.e. is -100 quot;higherquot; than
gt; -10
gt; ? If so, can't you just sort on this column in ascending order, so you
gt; go from -100 to -10 to 10 to 100?
gt;
gt; Or, do you mean that you want the sort order to go -10, -100, 10,
gt; 100
gt; ?
gt;
gt; Please clarify.
gt;
gt; Pete

I'd like the sort from top to bottom

-5 , -6, -9, -100, -300, 5, 8, 15, 100, ect--
Remotruker
------------------------------------------------------------------------
Remotruker's Profile: www.excelforum.com/member.php...oamp;userid=32082
View this thread: www.excelforum.com/showthread...hreadid=518379Assuming that column I is the one you want to sort on, you can add this
formula to J2:

=IF(i2lt;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:
gt; Assuming that column I is the one you want to sort on, you can add this
gt; formula to J2:
gt;
gt; =IF(i2lt;0,MIN(i:i) - i2 - 0.00001,i2)
gt;
gt; then copy down for as many items as you have in column I. You can now
gt; include column J in your sort range and sort on column J. Once sorted
gt; you can delete column J. This will give you the sort order you
gt; require,
gt; i.e. -5, -6, -9, -100, -300, 5, 8, 15, 100 etc.
gt;
gt; The reason for the 0.00001 is to ensure that the minimum value does
gt; not
gt; get treated as zero.
gt;
gt; I don't understand the formula in your opening post - you are dividing
gt; by 1(?)
gt;
gt; Hope this helps.
gt;
gt; PeteYes this helps so much . The divide by 1 was a mistake ,
i was dividing by -1 in a previous attempt.--
Remotruker
------------------------------------------------------------------------
Remotruker's Profile: www.excelforum.com/member.php...oamp;userid=32082
View this thread: www.excelforum.com/showthread...hreadid=518379Ok, thanks for feeding back.

Pete