Array formula normally breaks if it resides in any cell within the range we sort. Can we stop array formula messing up in sorting in Google Sheets?
In most of the cases, we can stop array formulas messing up in sorting. The simple solution is moving the array formula one row above the sort range.
I will explain how to tune the formula for that. But that won’t work in all the cases. Here is one example of an array formula that breaks in sorting in Google Sheets.
Sample Data and Array Formula Before Sorting:
As you can see there is an array formula in cell C2 which returns the grades of students based on their scores. It’s a nested IF array formula that works as below (not a formula explanation).
If the ‘Total Mark Out of 1000’ of a student is >900 the formula assigns grade “A” to that student in column C. For the marks > 700 the formula assigns grade “B”, if the mark is >500 the formula assigns grade “C”, else grade “D”.
I have assigned the grades using one array formula, right? Now I want to sort the student names in alphabetical orders.
That I can do by selecting the range A2:C8 and sort the data by clicking the Data menu Sort range by column A, A->Z.
After sorting check the column C. You can see that the sorting has broken the array formula in that column. It’s because of the row containing the array formula moved down.
Sample Data and Array Formula After Sorting:
Solutions to Array Formula Messing up in Sorting
To stop array formula messing up in sorting in Google Sheets as above, we can use one trick. What’s that?
It is entering the array formula outside the sort range! Instead of key the array formula in cell C2, based on my example above, you can use the formula in C1.
That means to avoid sorting messing up array formula, enter the array formula in the header row that contains column names.
But you can’t use the formula as it is. You may need to make some minor changes in the formula as per the below syntax.
={"column name";arrayformula}That means you should add a column name with the array formula. How?
There are two options/approaches to do this.
- If you sort your data using the Data menu Sort command, use option # 1 or 2 below.
- If you sort your data either by using the Data menu Sort command or the Data menu Filter view Sorting, you can follow option # 2.
You can use my methods (option # 1 or option # 2 below) to stop ‘any’ array formulas messing up in sorting. I have chosen the nested IF array for the example purpose.
Option # 1
Sorting Breaks Array Formula (How to Stop)
As per the above-given syntax and based on my example, here is the array formula that won’t mess up in sorting in Google Sheets.
={"Grade";ArrayFormula(if(B2:B>900,"A",if(B2:B>700,"B",if(B2:B>500,"C",if(B2:B>0,"D",)))))}This formula should be keyed in cell C1, not in cell C2. Since you are not sorting row 1, the formula won’t break in sort.
Option # 2
Filter View Sorting Mess up Array Formula in Google Sheets (How to Stop)
The option # 1 formula has one issue at it will break if you sort the data using the Data menu Filter views.
I have sorted column A, A->Z using the sorting option in Data > Filter view. This time the sorting messed up the array formula in column C.
Is there any solution that works both in the above two scenarios (option # 1 and option # 2). Yes! Use this formula.
=ArrayFormula(if(row(A:A)=1,"Grade",if(B:B>900,"A",if(B:B>700,"B",if(B:B>500,"C",if(B:B>0,"D",))))))The key here is, do not use an open range like A1:A or B1:B in the array formula. Instead use the open range like A:A, B:B.
That’s all about how to stop array formula messing up in sorting in Google Sheets.
Related Reading:
Hi Sir,
I am sending the link for the file.
[Spreadsheet ID]
Thanks,
Pradeep
Thanks for sharing your sheet. You can use my most recent formula. Just replace
DescwithAscin the QUERY.Hi,
Thanks a lot! In hindsight, I think I should have been able to figure it out, but as I mentioned before, I’ve never used GS before, so the syntax was a bit too much for me.
Thanks again!
Pradeep
Glad to be of help.
Hi Prashanth,
I am so happy to tell that your solution worked perfectly. I am sorry that I posted this on the wrong topic page because I thought it was more to with array sorting rather than the monthly output; as I was getting it anyways with my formula.
Thanks again!!
Hi Prashanth,
I am trying to adapt your array sort method for my query.
=ARRAYFORMULA(SORTN(TEXT(GOOGLEFINANCE(B2, "close", TODAY()-1000,TODAY(),1),{"yyyy mm ", "@"}), 9^9, 2, 1, 0))
I am using this formula to pull down the closing price of a stock. The output data is in this format:
Date Value
2023-08 100
2023-07 106
…and so on.
However, I want to present this output in reverse order. The oldest date should be first, and the most recent date should be last.
I appreciate your help in advance.
Hi Pradeep,
I believe you posted your question in the wrong topic.
The issue with your formula is that the text formatting is preventing you from sorting the data in the desired order.
I suggest you use the following formula, which is the correct way to achieve your desired result.
=LET(gf,QUERY(GOOGLEFINANCE(B2, "close", TODAY()-1000,TODAY(),1),"Select * Offset 1",0),SORTN(HSTACK(EOMONTH(INT(CHOOSECOLS(gf,1)),-1)+1,
CHOOSECOLS(gf,2)),9^9,2,1,1))
You can find this approach in my EOMONTH function under the subtitle “The Role of EOMONTH in QUERY.”
I wanted the last date of the month instead of the first date.
I am new to Google Sheets, and there is nothing like this in Microsoft Excel. I hope you will be so kind as to help me again.
Many thanks,
Pradeep
I understand. Here is the formula. It will return the actual month-end date in the GOOGLEFINANCE result with timestamp, instead of the month-end dates.
=LET(data,QUERY(GOOGLEFINANCE(B2, "close", TODAY()-1000,TODAY(),1),"Select * Order By Col1 Desc Offset 1",0),
SORTN(data,9^9,2,EOMONTH(CHOOSECOLS(data,1),-1)+1,1))
Dear Prashanth,
Sorry to bother you again. This formula is also giving the closing prices of the actual month-end date. But I need the closing price on the first day of the month.
Could you take a second look?
Thanks
Pradeep
Please create a sample sheet with sample data and hand-entered expected results. Copy the URL and share it in view mode below.
Hi Prashanth, great post!
Just have one question,
How would you include a SUM of all the values just below the header without:
– Messing up the SUM row order
– Getting a circular dependency
Hi, Damien Lemaitre,
I didn’t get “Messing up the SUM row order.”
Please clarify and also share an example.
This is a great option, I can’t seem to get the array to calculate the last row in the column.
=ArrayFormula(if(row(A:A)=1,"ID",IF(isblank(H:H),"",ifna(vlookup($G$2:$G,Codes!$I$2:$J,2,false))&" - "&
ifna(vlookup($D$2:$D,Codes!$C$2:$D,2,false))&
ifna(vlookup($E$2:$E,Codes!$E$2:$F,2,false))&
ifna(vlookup($F$2:$F,Codes!$A$2:$B,2,false))&$V$2:$V&
ifna(vlookup($C$2:$C,Codes!$G$2:$H,2,false))))
)
There is a value in H, the result for the last row is that it only puts in the ” – ” but nothing from the lookup values.
If I add another row at the bottom, it works fine with the previous last row.
I figured it out. I had the G2:G type reference instead of G:G.
Is there a way to prevent it from putting 0’s all the way to the end of the column? What did I miss?
here is my formula:
=ArrayFormula(if(row(A:A)=1,"Days Rest",IF(J:J<21,"0",if(J:J<36,"1",if(J:J<51,"2",if(J:J<65,"3",if(J:J<86,"4")))))))I also have this one in the next column, any idea how to make it a date format?
=ArrayFormula(IF(row(A:A)=1,"Eligible Date",(To_Date(C:C+K:K))))Fixed it adding the initial IF to check ISFBLANK.
=ArrayFormula(if(row(A:A)=1,"Days Rest",IF(isblank(J:J),"",IF(J:J<21,"0",
IF(J:J<36,"1",if(J:J<51,"2",
if(J:J<65,"3",if(J:J<86,"4"))))))))
Still working on the date column.
Hi Steven,
Maybe by including the DATEVALUE function and an IFERROR.
=ArrayFormula(IFERROR(IF(row(A:A)=1,"Eligible Date",if(datevalue(C:C),To_Date(C:C+K:K)))))Best,
Thanks, still just a number. 🙁
This seems to work:
Put the to_date at the beginning.
=ArrayFormula(to_date(IFERROR(IF(row(A:A)=1,"Eligible Date",if(Datevalue(C:C),(C:C+K:K))))))Thanks!
Hi, SKS,
I am glad that you find the tips useful.
Best,
Great post.
I had faced this problem many a time but was not able to find any solution.
This trick will be really helpful.