CODEWITHSUNDEEP
excelexcel-formula
hyg17
hyg17

Reputation: 237

Finding the maximum value among the products in two rows

I am an excel beginner and I would like to do the following.

Let row1= (a_1 a_2 a_3) and row2= (b_1 b_2 b_3).

I want excel to calculate the largest number among the products (a_1b_1, a_2b_2, a_3b_3).

It is very difficult to look up these things for I am not sure what kind of calculation I am doing and it is hard to explain.

Upvotes: 0

Views: 3035

Answers (3)

Panthersfball234
Panthersfball234

Reputation: 95

you can do that, or make a pivot with the raw data and get the MAX/MIN/AVG, based on the pivot options. I tend to use that instead and then vlookup the ID to the pivot to get whatever aggregate you need.

Upvotes: 0

m0r0dan
m0r0dan

Reputation: 149

The following formula, array-entered, gives you the result of the largest number among the products:

{=MATCH(A1:C1*A2:C2)}

(provided your data is in A1:C2 in the form you presented it).

For explanations on how to insert array formula in excel see e.g. this microsoft link; in short, you type the formula without the curly brackets and confirm with CTRL+SHIFT+ENTER instead of only ENTER.

If you want to find where this couple of numbers is (in your case: which column), I would try this:

{=MATCH(MAX(A1:C1*A2:C2);A1:C1*A2:C2;0)}

(also array-entered).

Upvotes: 1

Saravana Kumar
Saravana Kumar

Reputation: 394

Take a third column, C and enter formula in C1 as $A1*$B1. Pull it down vertically to all other rows so that row number gets incremented for each.

Then in the fourth column, use the formula MAX(C:C)

Upvotes: 2

Related Questions