Google Sheets – How to Divide One Column by Another

google sheets

I am rather new to spreadsheets, and I feel like what I want to accomplish is rather simple, but I am having enormous difficulty finding anyone else doing something similar.

I have values in column A and B, and I want to divide A2 by B2, A3 by B3, etc. and output to column C. Putting this in C2 DOES work:

=ARRAYFORMULA(A2:A/B2:B)

But the rest of column C all the way down displays #DIV/0!. A little research uncovered the IFERROR function. Perfect! So I tried:

=IFERROR(ARRAYFORMULA(A2:A/B2:B))

But this does not change the output. Still #DIV/0! all the way home. Am I using IFERROR wrong? Is there a way to select on the cells that have values? Is there another solution?

Best Answer

Use this formula instead.

Formula

=ARRAYFORMULA(IFERROR(A2:A/B2:B))

Note

Error catching has to take place inside the ARRAYFORMULA

Related Solutions

Google-sheets – Sum of numerical values excluding formulas in a Google Spreadsheet

If you use SUBTOTAL function in place of your SUM functions then subsequent SUBTOTAL functions will ignore the numbers returned by other SUBTOTAL functions, hence avoiding double counting.

For example if A5 has this formula

=SUBTOTAL(9,A1:A4)

[the first argument dictates the function, 9 = SUM]

then if you use this formula in A6

=SUBTOTAL(9,A1:A5)

you will see that the two formulas produce the same results because the second one ignores the value returned by the first formula. This works identically in Excel and google spreadsheets

Google Sheets – Convert One Column of Multicolumn Output REGEXREPLACE from String to Number

Depending on the details of your use case, one of the following may be preferable:

Add a column with =ARRAYFORMULA(VALUE( B1:B100 )), assuming B1:B100 is the first output column. Then sort by this added column. This may be the easiest way to achieve the sort that you want.
Apply Value only to the first output. To my understanding, this requires splitting the regexreplace, so that first the replacement is made, then Value is applied, and only then the embedded array {...} is formed:

=ARRAYFORMULA({VALUE(REGEXREPLACE(A1:A100,...,"$1")), REGEXREPLACE(A1:A100,...,"$2")} )

If you choose this path, you might as well have two different formulas in two columns, one =ARRAYFORMULA(VALUE(REGEXREPLACE(A1:A100,...,"$1"))) and the other =ARRAYFORMULA(REGEXREPLACE(A1:A100,...,"$2")). I think this version is the most readable and flexible one.

Apply Value whenever it doesn't throw an error (regardless of columns). This could be done with IfError:

=ARRAYFORMULA(IFERROR(VALUE(REGEXREPLACE(A1:A100,...,{"$1", "$2"})),REGEXREPLACE(A1:A100,...,{"$1", "$2"})))