5

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?

3 Answers 3

3

Use this formula instead.

Formula

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

Note

Error catching has to take place inside the ARRAYFORMULA

1
  • 1
    I suppose I didn't mention it, but I need the ability to add new data without changing the formula every time. This is what I was looking for. Thanks so much!
    – ricky
    Mar 4, 2014 at 15:26
3

Just for completeness:

In C2 you can write:

=A2/B2

then copy the cell (CTRL-C) and paste it (CTRL-V) to the whole column where you want the result to be visible. This will procude the same result as ARRAYFORMULA() and is in general more effort, but allows you to select the rows where you want to have the result.

Alternatively you can also provide the last row you want to calculate over to arrayformula.

=ARRAYFORMULA(A2:A27/B2:B27)

This would only print in lines 2-27.

0

Dividing one column by another

I like to keep it simple. Here is what worked for me.

In cell C2, I used the formula : 1

=IFERROR(A2/B2)

Then – after clicking the C2 cell – I grabbed the small blue square in the bottom right corner of the cell and dragged it down the whole column. 2

Grab the blue square and drag it down to fill the column.


Is there another solution?

– When I tried this solution, it first seemed to work. But when I tried using the same formula in all cells of the column, I got a #REF! error similar to what is reported here.

When using ARRAYFORMULA, I got a #REF! error.

References


1 Please replace the column letters with whatever suits your case.

2 I didn't see any reason to use ARRAYFORMULA.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.