Geek Answers Handbook

Sort formula formula excel?

I made an excel column with the formula. Now I would like to sort this column by number ... Just using sort will not work, because it sorts the formula (= if (; = abs (etc.), and not the numbers I see. Maybe someone any help me with this please?

+7
source share
4 answers

Yes, you can sort Excel / LibreOffice cells using only formulas.

  • My unsorted numbers (or words) are listed horizontally. e.g. B29 - G29 (6 numbers). I choose 29 so that it is not confused with 1 used in the RANK function: D

  • My sorted numbers should be in cells J29-O29.

  • Formula for cell J29

= IF (RANK ($ B29, $ B29: $ G29.1) = 1, $ B29, IF (RANK ($ C29, $ B29: $ G29.1) = 1, $ C29, IF (RANK ($ D29, $ B29: $ G29,1) = 1, $ D29, IF (RANK ($ E29, $ B29: $ G29,1) = 1, $ E29, IF (RANK ($ F29, $ B29: $ G29, 1) = 1, $ F29, IF (RANK ($ G29, $ B29: $ G29,1) = 1, $ G29, $ B29))))))

  • The formula for cell K29 ... just converts everything "=1″ to "=2″

  • The formula for the rest is "=3″ for L29, etc. to "=6″ for O29.

  • The RANK function will evaluate each cell in the range. There will be no unpaid.

  • The last part .., IF(RANK($G29,$B29:$G29,1)=1,$G29,$Q29) If there are more than one number, then there are more than one number of the same rank .. it will duplicate the previous one number of the same rank.

  • To sort words, you first need to convert the words to ASCII using the CODE function. The RANK function only works with numbers.

+3
source

Are you trying to take the output value from the formula field and then sort it in the next column?

If yes, try the following:

  =large(b$1:b$5000,rows($a$1:a1)) rows($x$1:x1) =1 dragged down becomes rows($x$1:x2) ie =2

It counts the number of rows in a range.

0
source

If you are looking for sorting numbers in a separate column, you can try the following:

 =LARGE($B$1:$B$5000,COUNT(B1:B5000)

=LARGE(ARRAY,K) this will return the kth largest number in the given array here =COUNT(B1:B5000) will return 7, which is the value of k.

Our final formula will look like this: =LARGE($B$1:$B$5000,5000) , which is the smallest number in the entire range.

Press Ctrl + Enter , choosing a range from b1: b5000 so that it sorts the numbers from b1: b5000.

0
source

Be careful when sorting rows using the formula as an index key. Sort will use the correct formula value to sort the rows. However, the formulas will remain unchanged and will be reevaluated.

Example: row 2 is on you, colum B: = VLOOKUP(E2,BW!A$2:D$93,3) - distribute the formula to each line below - after sorting, row 4 can be displayed first in a new list - then row 1 columb B : = VLOOKUP(E4,BW!A$2:D$93,3) - note that E4 in the formula does not indicate E1, as you might expect.

0
source

More articles:


All Articles