# Calculating =Max() but excluding the Max!

#### sweater_vests_rock

##### Well-known Member
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.

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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.

thanks.

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

Try...

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

Hope this helps!

domenic.

thanks, i was zoning out!

ben.

Replies
5
Views
212
Replies
1
Views
90
Replies
1
Views
224
Replies
8
Views
266
Replies
0
Views
261

1,203,189
Messages
6,054,002
Members
444,696
Latest member
VASUCH

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back