Sorting a column according to the values - not according to formulas

Eilenach

New Member
Joined
Jun 13, 2008
Messages
20
I have values (every single on is an average of two other values in column B) in column E, which I would like to sort according to size; just like in column H. The problem is that column H just contains the numbers. Because I have not managed to get the Sorting-function to work with the content of column E. I don't know if it is because the values are in every second cell (not in each cell), or because the value in the cell is directly achieved through a formula (the average formula). I would be grateful if somebody could figure out how I can sort the values maintaining the average-formula!
Model_final.xls
DEFGH
1HoursAverageperhourHoursSortedav.p.h
2
31500,00
41104,352500,00
53500,00
62165,354500,00
75500,00
83324,386500,00
97500,00
WD_durcu
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Re: Sorting values which are located in every second cell

if you're saying you want H3 to be the lowest value in the avg values (E) you could use the following

in H3
=SMALL($E$3:$E$10,$G3)

and copy down...

not sure I totally understand the question...
 

Eilenach

New Member
Joined
Jun 13, 2008
Messages
20
Re: Sorting values which are located in every second cell

I think you missunderstood me. Let me try again:

I simply want the values of Column E sorted according to size. But when I use the sort function, it doesn't sort correctly. I posted an example showing this.

(Column H is just an example what it should look like. Here are only the numbers)
Model_final.xls
EFGH
1AverageperhourHoursSortedav.p.h
2
31500,00
4104,352500,00
5168,983500,00
6165,354500,00
7244,385500,00
8324,386500,00
9223,147500,00
10150,578500,00
11135,979500,00
12135,9710500,00
1311500,00
1412500,00
1513500,00
1614500,00
1715500,00
1816500,00
1917500,00
2018500,00
WD_durcu
 

Eilenach

New Member
Joined
Jun 13, 2008
Messages
20
Re: Sorting values which are located in every second cell

I think I know what the error source is at least now. It seems to sort not according to values, but to the formula (B3, B4, B5...)! How can I change this?
 

Eilenach

New Member
Joined
Jun 13, 2008
Messages
20

ADVERTISEMENT

I now noticed that the cell references have changed! This of course mixes everything up! But how can I "lock" them? If I use $, I'm not able copy the Formula for all of Column E (which is very long)?
 

Eilenach

New Member
Joined
Jun 13, 2008
Messages
20
Would it be possible to insert the $-sign in the formulas afterwards? Or is there another solution?
 

Eilenach

New Member
Joined
Jun 13, 2008
Messages
20
Problem solved!

I found out that the search-replace can be set to formulas as well, so I used to insert $-signs afterwards!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,587
Messages
5,637,265
Members
416,963
Latest member
samfuge

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top