[SOLVED] Using a reference cell as part of a column in a google sheets query

Using a reference cell as part of a column in a google sheets query

I am using the following formula:

=query('Sheet1'!A1:EA173, "select C, Y, SUM(X), SUM(Z), (3.75-Y)*SUM(X) where X>0 and not C matches '"&TEXTJOIN("|", 1, Z:Z)&"' group By C, Y label Y 'Original Price', SUM(X) 'Total lbs.', SUM(Z) 'Original Payment'")

Which works fine, the problem is I'd like to change the number 3.75 to a cell reference for for the fourth column, like so:

=query('Sheet1'!A1:EA173, "select C, Y, SUM(X), SUM(Z), ('"&I1&"'-Y)*SUM(X) where X>0 and not C matches '"&TEXTJOIN("|", 1, Z:Z)&"' group By C, Y label Y'Original Price', SUM(X) 'Total lbs.', SUM(Z) 'Original Payment'")

The error I get is:
Unable to parse query string for Function QUERY parameter 2: Can't perform the function difference on values that are not numbers
Type(I1) is 1, so it's a number, but if I use the same query but change ('"&I1&"'-Y)*SUM(X) to just ('"&I1&"') the type of the cells in that column is 2 - so I'm assuming that's where the problem is. My question is how do I get it to read I1 as a number so that I can use it in my formula?
Thank you

Solution

try:

=QUERY('Sheet1'!A1:EA173, 
 "select C,Y,sum(X),sum(Z),("&I1*1&"-Y)*sum(X) 
  where X>0 
    and not C matches '"&TEXTJOIN("|", 1, Z:Z)&"' 
  group by C,Y 
  label Y                   'Original Price',
        sum(X)              'Total lbs.',
        sum(Z)              'Original Payment', 
        ("&I1*1&"-Y)*sum(X) 'some header'")