Sorting formulas issue

2
votes

I have a table which consists of names (1st column) avarageifs (2-6 columns) and average formulas (7 column).

Table before sorting

Averageifs formula is taking the name to average values for each name:

=IFERROR(AVERAGEIFS(Grades!B:B,Grades!$A:$A,Rankings!$B14),"N/A")

"Total" average formula is:

=IFERROR(AVERAGE(C14:G14),0)

Then I am trying to sort it by "total" average, formulas are mixing up and referring to different rows.

Table after sorting

But it should refer to the same row. What happens after sorting and how can I fix it?

1
excelsortingexcel-formulaformula
Try using table for all of your table support.office.com/en-us/article/… – Linga
@Linga thank you, but converting into table didn't help.. – Sergey Ryabov
@pnuts Grades - is another tab in this workbook, and Rankings is a tab there the table is located. – Sergey Ryabov
@pnuts ok, as I said in Rankings tab there is a table we are talking about and formula is referring to the name in the same row. In Grades tab in column "A" there are few lists of the same names and next to names there are columns containing rankings I am transferring. Basically Grades is a data-sheet. – Sergey Ryabov

1 Answers

1
votes

Basically the references in the AVERAGEIFS formulae are getting swapped around and you don't want this to happen.

The easiest way round it would be to put the Totals column next to the Names column and just sort these two columns, leaving the other formulae in place.

If you don't want to do that, a fairly quick and dirty solution is to replace the AVERAGEIFS formulae with this so they always refer to the current row:-

=IFERROR(AVERAGEIFS(Grades!B:B,Grades!A:A,INDIRECT("Rankings!$B"&ROW())),"N/A")

See the discussion here