3

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?

1

Use this formula instead.

Formula

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

Note

Error catching has to take place inside the ARRAYFORMULA

  • 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 '14 at 15:26
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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