How to convert range text to number format when using QUERY and FILTER

Question

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?

Answer 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",)))

=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)),)))