CODEWITHSUNDEEP
excelexcel-formula
Artium
Artium

Reputation: 5329

Average of Maximum Between Columns

An excel table have two columns of grades. I would like to calculate the average grade but adding only the maximum grade of each row.

It is possible to do this by adding additional column that would contain max of each row and calculating the average of it. I am interested to know if there is a cleaner solution that relies only on formulas.

Upvotes: 0

Views: 258

Answers (2)

amoy
amoy

Reputation: 181

If you want to use the helper column, you could use the formula MAX().

Example: =MAX(A2:B2) would give you the larger of the two. Just copy down the column.

Then you could still use the AVERAGE() function to average the numbers in that column.

Upvotes: 0

user1923975
user1923975

Reputation: 1389

Let's say in cell A1, you have the heading "first" and in B1 you have "second"

In cells A2 to B4, put in your number grades. Use the following formula in D2.

=AVERAGE(IF(A2:A4>B2:B4,A2:A4,B2:B4))

Hit ctr+shift+enter so that the formula has curly brackets and looks like so

{=AVERAGE(IF(A2:A4>B2:B4,A2:A4,B2:B4))}

This is an array formula and so it's looking at A2, checking if it's higher than B2, if so, it takes A2, otherwise takes B2. It then does this again with A3 and B3 etc and builds up an array of the figures you need to calculate the average (but doesn't show them to you).

The average on the outside is then calculating the average of the numbers in the array.

This assumes your grades are number scores and not letters. Apologies if my assumption is incorrect.

Upvotes: 2

Related Questions