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

[Eilenach]

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
	D	E	F	G	H
1	Hours	Averageperhour		Hours	Sortedav.p.h
2
3				1	500,00
4	1	104,35		2	500,00
5				3	500,00
6	2	165,35		4	500,00
7				5	500,00
8	3	324,38		6	500,00
9				7	500,00
WD_durcu

 

[DonkeyOte]

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]

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
	E	F	G	H
1	Averageperhour		Hours	Sortedav.p.h
2
3			1	500,00
4	104,35		2	500,00
5	168,98		3	500,00
6	165,35		4	500,00
7	244,38		5	500,00
8	324,38		6	500,00
9	223,14		7	500,00
10	150,57		8	500,00
11	135,97		9	500,00
12	135,97		10	500,00
13			11	500,00
14			12	500,00
15			13	500,00
16			14	500,00
17			15	500,00
18			16	500,00
19			17	500,00
20			18	500,00
WD_durcu

 

[Eilenach]

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]

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]

Would it be possible to insert the $-sign in the formulas afterwards? Or is there another solution?

 

[Eilenach]

Problem solved!

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