MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Simple Sorting


Posted by Trevor on November 01, 2001 7:10 AM


Only three columns
a = Name of state
b = Sales
c = % of total sales

the problem seems to be that the division in column c is dependant on the sum at the bottom of column b. When I try to sort to put the state with the highest sales at the top of the list - the percentages get completely whacked. The first state alphabetically is AK (cell a2), sales (cell b2), percentage (cell c2) the formula in cell c2 is =sum(b2/b52). B52 is the sum of all the sales for each state -the formula in cell b52 is =sum(b2:b51). When I run the sort, state AK falls into the 11th position (cell a12) the formula in the percentage column (c) becomes =sum(b12/c62)

If I new how to send you this little spreadsheet - I would.


Posted by Todd on November 01, 2001 7:18 AM

No need to send the spreadsheet..
in c2 put =sum(b2:b$52) then fill down in col. c.
The $ fixes the row. If you filled this to the right, you could get c2:c$52. If you want to fix the col. also, then use =sum(b2:$b$52) and filling right would get c2:$b$52, etc.

Posted by Mark W. on November 01, 2001 11:54 AM

Trevor, cell C2 should contain the formula,
=B2/$B$52 which is then copied down to cell
C2. The SUM function is superfluous
because the expression, B2/$B$52, is a
single value. That's like saying, =SUM(1),
which is 1 -- with or without the SUM
function.


Posted by Mark W. on November 01, 2001 12:06 PM

Furthermore...

Sorting is like copying the formulas from one
cell to another. Remember when sorting cells
themselves aren't shuffled -- the formulas or
values within are. As with copying you must
use absolute cell references to insure
referential integrity. For more on this
subject use Excel's Help facility to find
the topic for "The difference between relative
and absolute references") Trevor, cell C2 should contain the formula,


Posted by Trevor on November 02, 2001 5:34 AM

that did work - thanks much. One other little thing - when I run the sort - that B52 column moves as well, is there a way to lock it or do I just select the range I want in the sort and not include that row?

I just worry about passing this on to others who are even less adapt than I am with excel.

No need to send the spreadsheet..


Posted by Trevor on November 02, 2001 5:39 AM

Re: Furthermore...

I will certainly investigate the relative and absolute differnces for future use. Things are working much better now. One further question, when executing the sort, I cell containing the total (b52) also moves - I know that I can select the range of cells to sort and not include that row, but is there a way for me to hold that in place. This form (which is much longer and more involved containing consumer price index and spending information from the lastest available census data) will be sent out to people even less adapt than I am at excel.

Sorting is like copying the formulas from one


Posted by Todd on November 02, 2001 6:09 AM

If the row 52 is a total row, then it shouldn't be sorted.

If you're passing it on to others who are less adept than you, and they need to make changes, you should probably include some documentation on how to do it. that did work - thanks much. One other little thing - when I run the sort - that B52 column moves as well, is there a way to lock it or do I just select the range I want in the sort and not include that row? I just worry about passing this on to others who are even less adapt than I am with excel.


Posted by Mark W. on November 02, 2001 7:22 AM

Just...

...insert a blank row between your data list and
the cell contain the sum total. The sort utility
will then recognize that the total isn't a part
of your data list. I will certainly investigate the relative and absolute differnces for future use. Things are working much better now. One further question, when executing the sort, I cell containing the total (b52) also moves - I know that I can select the range of cells to sort and not include that row, but is there a way for me to hold that in place. This form (which is much longer and more involved containing consumer price index and spending information from the lastest available census data) will be sent out to people even less adapt than I am at excel.


Posted by trevor on November 02, 2001 7:45 AM

Re: Just...

Sometime just the simpliest little things . . .

...insert a blank row between your data list and