Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I would like to multiply, row-by-row, two columns in an Excel sheet (in order to take the median of the resulting column).

1  3
2  5
4  2

Should become

3
10
8

How can I do that in a formula (i.e. without creating a separate column with the multiplication result)?

share|improve this question
up vote 6 down vote accepted

You want an array formula...

Assuming your data starts in A1, you can type:

=MEDIAN(A1:A3*B1:B3)

and the press Ctrl + Shift + Enter to enter it into the cell as an array formula. If you do it correctly, you'll see the formula enclosed in curly braces by Excel.

share|improve this answer
    
Thanks a lot! I didn't know you had t use ctrl+shift+enter for that. – Jonas Jun 22 '10 at 11:10

In excel, you can multiply cells together in the median formula to get the result.

The median of {3, 10, 8} = 8 in excel you can use Median(A1*B1, A2*B2, A3*B3) to get the same result. Provided the first number "1" starts on A1.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .