Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Maximum of Two Numbers in Array
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Maximum of Two Numbers in Array
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 "cardan" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Maximum of Two Numbers in Array
Thats it! Thank you! I was putting the MAX function on the outside of
the SUMPRODUCT, rather than within. Thanks again! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
finding maximum, minimum in a range consists both Positive and Negative numbers | Excel Worksheet Functions | |||
Finding Location of Maximum Value in 2D Array | Excel Discussion (Misc queries) | |||
Finding Location of Maximum Value in 2D Array | New Users to Excel | |||
Finding Location of Maximum Value in 2D Array | Excel Worksheet Functions | |||
Finding Maximum | Excel Worksheet Functions |