excel 2016: how to sort a column when the column cells are the sum of 5 adjacent cells

Question

I am trying to sort col A. The Cell A1 is sum(B1 + C1 + D1 +E1). This is a scoring system where col 'A' is the total.

Each of B1/etc is the result of an IF statement evaluating ranges elsewhere in the worksheet. B1/etc will range from +/- 2, +/- 1, 0. So, a given cell in col'A' can range from +8 to -8.

problem: I find that sorting a column with an embedded formula [like the 'sum' formula] is not accurate, i might get +8, +8, +3, -3, +7, etc. if i copy/paste cell values from the sum into a spare column, then a sort of largest to smallest, the sort result is accurate.

i don't understand what is happening. so i am having some trouble trying to fix this. again, i am sorting rows in a 'table', using the values in cells in 1 column, where each cell in the column is a sum of 4 adjacent cells in the same row [this sum is done row by row via formula; the 'table' propagates the formula down the rows].

seems simple enough; but i can't figure out what is the root cause.

thanks, ron

Answer 1

Hope I understand you correctly here. Basically, you just want to numerically sort the results of a formula on a spreadsheet where the numbers are on column 'A'?

For some reason it isnt working for you?

Regardless, here is what I came up with, let me know if I am off or need more work:

Starting with cell A2 and continuing with the rest of column A's formulas change them to this:

In Cell A2, the formula would be: =Sum(B2:E2).

In Cell A3, the formula would be: =Sum(B3:E3).

And continue on with that down the rest of the columns

Once finished, put a title in Cell A1, go to your Data Tab and click the Filter button, from there you can then click on the drop down button in Cell A1then select the filter option that you need (IE: Smallest to Largest, or Largest to Smallest).

See screenshot below:

Screenshot

Hopefully this helps!