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

D

DrDebit

Board Regular
Joined
May 20, 2013
Messages
123
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
Perhaps

=MAX(MAX(C1:C3/A1:A3),MAX(C8:C9/A8:A9))
Confirm using Control + Shift + Enter for array formula
 
Upvote 0
You would make your life easier if they were contiguous ranges.

If the other cells don't contain numbers that would give erroneous results,

=MAX(IFERROR(C1:C9/A1:A9,-9E+307))

... confiirmed with Ctrl+Shift+Enter
 
Upvote 0
Is it possible to combine either of these with the MATCH function to determine which quotient is the maximum?
Again, many thanks.
 
Upvote 0
shg said:
If the other cells don't contain numbers that would give erroneous results,

=MAX(IFERROR(C1:C9/A1:A9,-9E+307))

... confiirmed with Ctrl+Shift+Enter
Click to expand...
Only mentioning this because of my penchant for compact formulas/code... you can omit the -9E+307 from your formula and it should still work correctly (still needs to be confirmed with CTRL+SHIFT+ENTER)...

=MAX(IFERROR(C1:C9/A1:A9,))
 
Upvote 0
DrDebit said:
Is it possible to combine either of these with the MATCH function to determine which quotient is the maximum?
Click to expand...
I would be willing to bet that shg will be able to come up with a better formula than this one, but until then, this array-entered** formula will return the row number where the maximum quotient is located on)...

=MAX(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.
 
Upvote 0
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​
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Find Max Value by Group VBA
Replies
3
Views
478
DanteAmor
DanteAmor
SaraWitch
  • Solved
Return values based on multiple cells
Replies
4
Views
228
etaf
etaf
S
  • Question
Replace different text/strings in multiple files in one folder
Replies
1
Views
232
Peter_SSs
Peter_SSs
R
  • Solved
Connect periods with spilled result, where the result is same as the calculated result in previous row
2
Replies
15
Views
155
roelandwatteeuw
R
B
  • Question
VBA Macro - References Another Worksheet and repeats data based on a number in another referenced cell.
Replies
0
Views
182
BAQI
B

Forum statistics

Threads
1,214,647
Messages
6,120,722
Members
448,987
Latest member
marion_davis

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top