Excel- I can't sort becasue of an array. I have no knowledge of..in current version of Ms Office 365

Copper Contributor

see attached file 

 

 

3 Replies

@Mabby Check the formula in T269. That's what's blocking the sort. You, perhaps unintentionally, created a 3-cell dynamic array. I suspect you want to enter =F269 here and copy it down two rows.

Thank you for your help. You were correct and all is now well.
Can you please let me know how you found the error so that if it happens in the future I I can manage it.
With many thanks, Mabby

@Mabby Plain luck, I guess. On the Formulas ribbon, I selected "Show formulas", so that I could see what  you really have in all the cells. I noticed quite a few inconsistent formulae, but the one in T269 stood out. There's not really much more to it. 

Screenshot 2021-12-13 at 05.44.38.png

With respect to inconsistent formula, you may want to consider looking over columns E and F in particular. Quite a lot happening there which is bound to create problems. The screenshot below shows the content of just a small area. Notice that column E has formulae and constants and column F mostly has SUM formulae, and then suddenly just a reference to one cell. Better to devise a rule that allows you to calculate GST with a consistent formula (if possible) and use consistent formulae in columns that sum two other columns

 

With regard to the formulae themselves, =PRODUCT(D249,1/11) would be easier to read (in my opinion at least) if you would use =D249/11. Similarly, =SUM(D249,-E249) could simply be written like =D249-E249.

 

Screenshot 2021-12-13 at 05.34.38.png