Finding Maximum of Two Numbers in Array

C

cardan

Hello all,

I am having trouble writing a formula trying to find the Maximum of
two numbers in a row based upon a value in the same row. Essentially,
I have 3 columns. Column A has values between 1-5. Column B has
values between 0 and lets say 50. Column C also has values between 0
and 75. (same has Column B). These columns have around 100 rows of
data.

If say cell A1 equals 2, I need to add cell C1 and cell B1 together. I
need to do this for all the rows and then I need the formula to return
the maximum of columns B and C.

A1 = 2
A2 = 3
A3=2
A4=5
A5=4

B1 = 3
B2 = 4
B3 = 6
B4= 9
B5=2

C1 = 3
C2=3
C3=2
C4=4
C5=8

I need the formula to recognize the 2 in A1 and A3, and then compute
B1+C1=6 and also compute B3+C3=8 and then return the MAX of these two
which in this case equals 8.

Is this possible? Any help or guidance would greatly be appreciated.
Thank you in advance.

Respectfully, Dan
 
R

Rick Rothstein \(MVP - VB\)

Try this....

=SUMPRODUCT(MAX((A1:A100=2)*(B1:B100+C1:C100)))

although I would replace the 2 with a cell reference and put the lookup
number in it. For example, with 2 in D1...

=SUMPRODUCT(MAX((A1:A100=D1)*(B1:B100+C1:C100)))

Change the upper row limits on each of the ranges from 100 to a row number
greater than (or equal to) the largest row you expect to have data in.

Rick
 
C

cardan

Thats it! Thank you! I was putting the MAX function on the outside of
the SUMPRODUCT, rather than within. Thanks again!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top