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

#### Eilenach

##### New Member
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
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
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
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
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
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

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
Would it be possible to insert the \$-sign in the formulas afterwards? Or is there another solution?

#### Eilenach

##### New Member
Problem solved!

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

Replies
1
Views
336
Replies
6
Views
173
Replies
1
Views
221
Replies
18
Views
271
Replies
8
Views
318

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.

### Which adblocker are you using?

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

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