I have columns A, and B, and I want to divide every value in A by the values in B. For example, divide 15 by all values in column B, once done, shift down and divide 57 by all values in column B etc. Does anyone know how to do this?
-
2For starters, we'd need to know where you plan for this output to go (which would require a 2D grid with as many rows as you have values in Col A and as many columns as you have in Col B). Really, if you set up your posted Col-A values in a column (say, A2:A8) and your posted Col-B values across a row (say, B1:H1), you could get the results you want with a single formula in B2. We just don't have enough pieces yet to efficiently and effectively offer a solution.– Erik TylerCommented Oct 23, 2021 at 1:52
Add a comment
|
1 Answer
With the dividends in column A2:A10 and the divisors in B2:B10, put this in cell C2:
=arrayformula( iferror( 1 / (A2:A10 / transpose(B2:B10)) ^ -1 ) )
answered Oct 23, 2021 at 9:25
-
that has a little error, try: <pre> =arrayformula( iferror( 1 / A2:A10 * transpose(B2:B10) ) ^ -1 ) </pre>– DanieleCommented Oct 23, 2021 at 10:35
-
I tested you answer and there is a problem. But @DANIELE noticed a misplaced closing bracket - I tested that correction and it is right. I've edited the answer to correct the closing bracket from "after" to "before" the exponential operator.– TedinozCommented Oct 23, 2021 at 11:09
-
Edited the answer to add the missing parentheses in the canonical way. Commented Oct 23, 2021 at 18:41
-
@doubleunary the correct formula is
=arrayformula( iferror( 1 / A2:A10 * transpose(B2:B10) ) ^ -1 )- there's only one closing bracket following the digit "-1". I think you edited my edit ;) Better that I hadn't done that at all. lol– TedinozCommented Oct 24, 2021 at 1:32 -
@Tedinoz you are correct, but the problem is that when exponentiation is outside the
iferror(), empty rows and zeros will get+Infinityand show#NUM!. Using parentheses in the canonical way also anticipates that readers may apply the pattern to a calculation that involves addition or subtraction. Commented Oct 24, 2021 at 6:51