Finding the maximum of quotients of two non-contiguous cell groups...

[DrDebit]

I have a number in A1, A2, A3, A8, A9, and another set of numbers in C1, C2, C3, C8, C9.

I want to to find the maximum quotient from dividing the individual numbers in the second set by the corresponding numbers (for each cell) in the first set (sort of like a max of the sumproducts, but quotients).

In other words, I want max(C1/A1,C2/A2, etc.), but in my specific case the data sets are much bigger.

Thank you!

 

[Rick Rothstein]

Row\Col	A​	B​	C​	D​	E​
1​	0​		1​	#DIV/0!​	D1: {=MAX(IF(C1:C3/A1:A3=MAX(IFERROR(C1:C3/A1:A3,)),ROW(C1:C9)))}
2​	1​		2​	2​	D2: {=MAX(IFERROR(C1:C3/A1:A3,-9E+307))}
3​	2​		3​
4​

Ah, good point about the division by zero error. Here is my (still array-entered**) formula, corrected to avoid that...

=MAX(IF(A1:A9=0,,IF(C1:C9/A1:A9=MAX(IFERROR(C1:C9/A1:A9,)),ROW(C1:C9))))

**Confirm this formula using CTRL+SHIFT+ENTER and not just Enter by itself