I want to output a row with the maximum value of the specified column.

Asked 1 years ago, Updated 1 years ago, 460 views

month, day, count
10  1   5
10  2   6
11  3   7
11  4   8

There are numeric data for each date such as
I'd like to print a line that records the maximum monthly value.

Findings

1026
11  4   8

6
every 2 days in October November is 8
on the 4th. That's what it looks like

Only the month and maximum values are shown in the table

=query(A:C, "select A, max(C)where AIS NOT NULL group by A", false)
max     
10  6
11  8

And so on, but how can I print out the date when I take the maximum value?

https://qiita.com/nogitsune413/items/f413268d01b4ea2394b1
With SQL, I think I can do it by JOINing with subqueries.
Is there a way to do it with a spreadsheet?

Or is it possible to output the results of =query at intervals like the first and third columns?

I think I can fill the date separately if I use lookup.
The query results will be stuck in the previous two columns, so
I'd like to put the date in the second row between

google-spreadsheet

2022-11-30 10:45

1 Answers

How about a sample like the one below?

Sample formula:

=BYROW (UNIQUE (FILTER(A2:A, A2:A<>")), LAMBDA(R, SORTN(QUERY(A2:C, "SELECT* WHERE A="&R), 1, 0, 3, FALSE))
  • This is the flow in which the Unique value is retrieved from column A, sorted by group, and column C is the maximum row.

Testing:

If the table shown is header included and cell A1:C5, for example, if you put the above formula in cell E2, you will get the following results:

Enter a description of the image here

References:


2022-11-30 23:18

If you have any answers or tips


© 2024 OneMinuteCode. All rights reserved.