#arrayformula

엑셀에서는 배열수식이라는 것을 가지고 벡터연산이나 행렬연산을 할 수 있는데, 일련의 자료가 오름차순 정렬인지, 아니면 내림차순 정렬인지 아예 정렬된 상태가 아닌 지를 판단하는데 이용할 수 있다. 지난 포스팅(‘꺽이기 직전의 값을 찾아라’)에서 써 먹은 5,6,2,7을 다시 보자. 5,6,2,7은 한 눈에 보기에도 정렬되지 않은 숫자이다. 그러면 순서를 바꾸어 2,5,6,7은 어떠한 가? 오름차순으로 정렬되어 있다. 그리고 7,6,5,2라고 하면 내림차순으로 정렬되어 있다. 이제 우리는 {2,5,6,7}, {7,6,5,2}, {5,6,2,7} 등등이 우리의 뇌가 아닌 엑셀이 판단할 수 있도록 해주는 방법을 알아본다. 위의 그림에서 {2,5,6,7}을 {2,5,6}과 {5,6,7}로 나누고 각 원소별로…

Google Sheets. Data Validation Rule now supports $$

Photo by Kaushik Panchal on Unsplash What has changed The data validation rules now have the ability to make relative links. If you copy a drop-down list with a link to a range, then the range will be changed according to the same rules as inside formulas. = $A$1 – the link does not change when copied. $ before a letter anchors the column, $ before a number anchors the string.= $A1 – when…

I've got this table — list of all my WordPress posts — using formulas only 😀

I’ve got this table — list of all my WordPress posts — using formulas only 😀.

Here’s my sample Spreadsheet to COPY.

The process was as follows:

Are you…

Beginner: query formula for Bar Chart in Google Sheets

😇 Here’s the old tick I wanted to share. Copy Sample File!

You could create any Chart with a single QUERY formula or combination of QUERY+FILTER+SORT+UNIQUE

Here’s the chart:

The formula is:

=QUERY({A:C},"select Col1, sum(Col3) where Col2 <> '' group by Col1 pivot Col2"

{}— used to convert the range into the array. Now I can use Col1-notation for the formula and forget about the actual columns…

Get maximum values and delete duplicates with SORTN function

Thanks to anonymous who shared this answer on StackOverflow. I think this was a brilliant solution!

Case: a user needs to find countries with the maximum population by continent.

The data is as follows.

Notes:

need to find China in Asia because it is maximum for Asia.

need to preserve a word “China” and region name “Eastern Asia”.

cannot use [query] formula because the query will group all the…

Gantt Chart Template in Google Sheets

Make a copy and use your automated Gantt chart:

The chart is automated.

Fill the scale — the number of days you want to show in one cell. Minimal value is 1.

Fill the data — tasks, sub-tasks, start date and duration. Add more columns if needed.

Get automatic months and days — change date format in rows 2 if needed.

Get automatic Id — for tasks and sub-tasks.

Get automatic chart cells— change…

Create a Report with Internal Hyperlinks in Google Sheets

In this article, you’ll learn to make the query report with internal links:

Problem. You cannot edit the data in the report

Solution. Create dynamic links and follow the link if you want to edit a specific row.

  Short reference about internal links

The syntax available is:

=hyperlink("#gid=1166414895&range=A1", "link to A1")

The link works within the file. It leads to the cell without page…