Calculating =Max() but excluding the Max!

[sweater_vests_rock]

Hi everyone.

I have ~20 columns of data for about 80 rows of studies. For a certain column X, I have inserted a column Y and calculated (Value in Col X - Max(other columns)) for each study.

What I would like to do now is insert another column Z and calculate (Value in Col X - Max(other columns excepting the column returned by the previous Max calculation and column Y)). However, because the column I wish to exclude is not always the same for each study, I don't know how to do this!

Any ideas? See below for a rough visual.

Thanks. Ben.


Col A | Col B | Col C | Col D | Col X | Col Y | Col Z
100 | 200 | 300 | 400 | 300 | -100| 0
50 | 300 | 250 | 150 | 450 | 150 | 200
200 | 150 | 200 | 350 | 100 | -250 | -100

etc.

 

[PA HS Teacher]

If I understand your question correctly, you might be able to use the large function.

=Large(Array,n)

So for you first column, column Y n = 1
For your 2nd column, column Z n=2 etc.

The array should not change, it would be your original
$A1:$T80.

 

[sweater_vests_rock]

thanks.

is there a similar function which accepts non-contiguous ranges?

 

[Domenic]

Try...

=LARGE((Array1,Array2,Array3),n)

Hope this helps!

 

[sweater_vests_rock]

domenic.

thanks, i was zoning out!

ben.