1

I am using this formula in my example sheet to filter the 5 last columns of data in a chart: =INDEX(QUERY(TRANSPOSE(SORTN(TRANSPOSE(FILTER({B3:U3;B5:U7},B1:U1=A11,B2:U2=A12)),5,0,TRANSPOSE(FILTER(COLUMN(B1:U7),B1:U1=A11,B2:U2=A12)),0))&"","select Col5,Col4,Col3,Col2,Col1",))

This is working great, except for the fact that I am unable to use the filtered values in a graph since all the numbers are being formatted as text, and trying to change this via the menu options changes nothing.

I know I can change dates to a date value if I use something like this on one of the filtered ranges in the formula: TEXT(DATEVALUE(J3:3),"mmm dd"), but have been unable to find an equivalent for numeric values.

Does anyone have any ideas on how I can turn range B5:U7 to numbers?

player0
  • 124,011
  • 12
  • 67
  • 124
J. Kubassek
  • 205
  • 2
  • 14

1 Answers1

1

try:

=INDEX(IFERROR(
 QUERY(TRANSPOSE(SORTN(TRANSPOSE(FILTER({B3:U3;B5:U7},B1:U1=A11,B2:U2=A12)),5,0,
 TRANSPOSE(FILTER(COLUMN(B1:U7),B1:U1=A11,B2:U2=A12)),0))&"","select Col5,Col4,Col3,Col2,Col1",)*1, 
 QUERY(TRANSPOSE(SORTN(TRANSPOSE(FILTER({B3:U3;B5:U7},B1:U1=A11,B2:U2=A12)),5,0,
 TRANSPOSE(FILTER(COLUMN(B1:U7),B1:U1=A11,B2:U2=A12)),0))&"","select Col5,Col4,Col3,Col2,Col1",)))

enter image description here

enter image description here

=INDEX(IFERROR(
 QUERY(TRANSPOSE(SORTN(TRANSPOSE(FILTER({B3:U3;B5:U7},B1:U1=A11,B2:U2=A12)),5,0,
 TRANSPOSE(FILTER(COLUMN(B1:U7),B1:U1=A11,B2:U2=A12)),0))&"","select "&
 TEXTJOIN(",", 1, "Col"&SORT(SEQUENCE(IF(COUNTIFS(B1:1, A11, B2:2, A12)>=5, 5, 
 COUNTIFS(B1:1, A11, B2:2, A12))), 1, 0)),)*1, 
 QUERY(TRANSPOSE(SORTN(TRANSPOSE(FILTER({B3:U3;B5:U7},B1:U1=A11,B2:U2=A12)),5,0,
 TRANSPOSE(FILTER(COLUMN(B1:U7),B1:U1=A11,B2:U2=A12)),0))&"","select "&
 TEXTJOIN(",", 1, "Col"&SORT(SEQUENCE(IF(COUNTIFS(B1:1, A11, B2:2, A12)>=5, 5, 
 COUNTIFS(B1:1, A11, B2:2, A12))), 1, 0)),)))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • Is there an easy way to edit the formula so it still works if there are less than 5 columns found? I've experimented with putting IFERRORs in different places but I can't quite figure it out. – J. Kubassek Oct 04 '22 at 20:07
  • @J.Kubassek try: https://i.stack.imgur.com/PZB8w.png – player0 Oct 04 '22 at 21:46
  • This works great in our example, but do you have any ideas why I'm getting this error message when trying to implement it with my actual sheet? **Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: Col19** You can see this in Cell D8 on the 'Actual Dashboard' tab - Cell D2 works fine, but when I add another FILTER condition of _'Actual Data'!J10:10<>""_ it seems to break things, and I can't figure out why. – J. Kubassek Oct 05 '22 at 17:06
  • @J.Kubassek try adding 'Actual Data'!J10:10<>"" into those countifs too – player0 Oct 05 '22 at 20:20
  • I tried that, but then I get the error: **COUNTIFS expects all arguments after position 2 to be in pairs.** – J. Kubassek Oct 05 '22 at 21:22
  • 1
    @J.Kubassek in COUNTIF you will need to enter it as two arguments so instead `'Actual Data'!J10:10<>""` it will be `'Actual Data'!J10:10, "<>"` are you entering it this way? – player0 Oct 05 '22 at 21:24