not sorting in ascending order

%3CLINGO-SUB%20id%3D%22lingo-sub-2594966%22%20slang%3D%22en-US%22%3Enot%20sorting%20in%20ascending%20order%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2594966%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%2C%3C%2FP%3E%3CP%3EI%20have%20trimmed%20my%20numbers%20and%20the%20sorting%20still%20wouldn't%20work.%20Sheet%203%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2594966%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2596194%22%20slang%3D%22en-US%22%3ERe%3A%20not%20sorting%20in%20ascending%20order%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2596194%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1115559%22%20target%3D%22_blank%22%3E%40Wynton90%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESo%20you%20just%20need%20the%20formula%20in%20E2%3B%20you%20can%20delete%20the%20rest%20in%20that%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2596181%22%20slang%3D%22en-US%22%3ERe%3A%20not%20sorting%20in%20ascending%20order%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2596181%22%20slang%3D%22en-US%22%3EYes%20indeed%2C%20thks%20for%20spotting%20the%20mistake.%20I%20got%20misled%20because%20the%20formula%20actually%20gives%20numbers.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2596155%22%20slang%3D%22en-US%22%3ERe%3A%20not%20sorting%20in%20ascending%20order%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2596155%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1115559%22%20target%3D%22_blank%22%3E%40Wynton90%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EShouldn't%20that%20be%20a%20single%20number%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2596143%22%20slang%3D%22en-US%22%3ERe%3A%20not%20sorting%20in%20ascending%20order%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2596143%22%20slang%3D%22en-US%22%3EIt%20is%20to%20find%20the%20Euclidean%20distance%20between%20the%20Sales%20and%20Margin.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2594973%22%20slang%3D%22en-US%22%3ERe%3A%20not%20sorting%20in%20ascending%20order%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2594973%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1115559%22%20target%3D%22_blank%22%3E%40Wynton90%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20because%20of%20the%20formulas%20in%20column%20E.%3C%2FP%3E%0A%3CP%3EE2%20contains%20%3DSQRT(SUMXMY2(C2%3AC919%2CD2%3AD919))%3C%2FP%3E%0A%3CP%3EE3%20contains%20%3DSQRT(SUMXMY2(C3%3AC920%2CD3%3AD920))%3C%2FP%3E%0A%3CP%3E...%3C%2FP%3E%0A%3CP%3EE919%20contains%20%3DSQRT(SUMXMY2(C919%3AC1836%2CD919%3AD1836))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESo%20E2%20will%20always%20be%20the%20largest%20value%20(it%20sums%20all%20data%20rows)%2C%20and%20E919%20will%20always%20be%20the%20smallest%20value%20(it%20sums%20only%20row%20919).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20is%20the%20purpose%20of%20column%20E%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2594972%22%20slang%3D%22en-US%22%3ERe%3A%20not%20sorting%20in%20ascending%20order%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2594972%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1115559%22%20target%3D%22_blank%22%3E%40Wynton90%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorting%20works%20fine.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi,,

I have trimmed my numbers and the sorting still wouldn't work. Sheet 3

6 Replies

@Wynton90 

Sorting works fine.

 

@Wynton90 

That's because of the formulas in column E.

E2 contains =SQRT(SUMXMY2(C2:C919,D2:D919))

E3 contains =SQRT(SUMXMY2(C3:C920,D3:D920))

...

E919 contains =SQRT(SUMXMY2(C919:C1836,D919:D1836))

 

So E2 will always be the largest value (it sums all data rows), and E919 will always be the smallest value (it sums only row 919).

 

What is the purpose of column E?

 

It is to find the Euclidean distance between the Sales and Margin.

@Wynton90 

Shouldn't that be a single number?

Yes indeed, thks for spotting the mistake. I got misled because the formula actually gives numbers.

@Wynton90 

So you just need the formula in E2; you can delete the rest in that column.