Unable to Parse Formula Using MINUS Function in Google Sheets

Question

I have two columns in a Google spreadsheet. One column ("amountPaidByBuyer" or column "G") contains the amount buyer paid for product. The second column ("cost" or column "J") contains the amount our company paid for the product. I have a 3rd column ("ROI") which should contain the difference between the cost and the amount paid by buyer (for example, if cost is 10.99 and the buyer paid 11.99 then the ROI is +1.00. If the cost is 10.99 and the buyer paid 9.99 then the ROI is -1.00).

Since I'm simply subtracting two columns from the same row (G - J) I tried inserting the following formula:

=MINUS(G ROW(),J ROW())

This was done based on the following answer which suggests using the ROW() function to get the current row automatically (instead of needing to specify this value in the formula).

However, this leads to the following error: Formula parse error.

Google does not provide any additional details about the error.

What is wrong with the formula?

Answer 1

If you want to use minus and row(), this will work:

=minus(indirect("G"&ROW()),INDIRECT("J"&ROW()))

But why not use:

=G2-J2

Change the row to where you start then drag the formula down for rows below.

Answer 2

What’s wrong with the formula is too much for Sheets’ help features to cope with:

i. MINUS requires numeric parameters and G ROW() is not numeric.

ii. G ROW() is merely a string of characters, including a space. In Excel, for example, a space may be an intersect operator – but space does not function as such here. (Even with the update a few minutes ago Sheets does not have an intersect operator.)

iii. To combine a column reference with a row reference to make a cell reference concatenation is required (eg use of & operator).

iv. But G&ROW() does not work because Sheets does not recognise G as a column name, without quotes round the G (unless a column has been given the range name of G).

v. "G"&ROW() returns a cell reference, but not the value in that cell, so not the numeric parameter required by MINUS.

vi. To interpret the cell reference as the value within the cell a function like INDIRECT is required.