excelsortingcalculated-columns

AVERAGEIFS/SUMIFS formula is not sorting ASC or DESC

I am having trouble sorting an AVERAGEIFS column (and a SUMIFS column) DESC or ASC

ESSENTIALLY, I am looking at labor statistics data for 2017-2020; by industry, and year

=AVERAGEIFS(Data!F:F,Data!B:B,E4,Data!A:A,$C$16)

Is the formula. The formula is correct, and yielding the correct values. However, when trying to sort the values, it does not nothing and just refresh the exact values.

I've connected a chart to the results, which works well, however, I can't organize the bars descending since the source data is having the issue I referenced. I tried deleting the page references like some other forums suggested to no avail.

Any insight??

enter image description here

I tried deleting the page references like some other forums suggested to no avail,

I've used dynamic arrays for the industry and plain values to see...

** UPDATE** attaching a photo on what fixed it, thanks so much for everyone contributing! See second photo for answer

enter image description here

Solution

  • You can select the chart source data as a whole table and then sort with the sort dialog found in the Data ribbon. However, if the Industry labels are generated with a Dynamic array formula, using Unique() for example, then you cannot use a manual sort on the table. You can copy and paste/values the Industry labels, so they are no longer a dynamica array, then sort the table.

    enter image description here

    If you need a more dynamic solution, consider using a Pivot Table instead of a formulaic table. What you want to achieve can be done in a Pivot Table / Pivot Chart with just a few clicks, without writing any formulas.