How-to SUM(A1:A100)-SUM(LARGE(A1:A100,{1,2,..,n})) with n being a variable?

**Coume** · 03-22-2010, 10:06 AM

Hello,

I am trying to do the following but I can't think on how to do that...

I need to do the SUM of a column but remove the n largest values. The n value is calculated by another formula..
SUM(A1:A100)-SUM(LARGE(A1:A100,{1,2,..,n}))

Any idea on how to do that will be more than welcome.

Thanks
Ludo

**NBVC** · 03-22-2010, 10:11 AM

Try:

=SUM(A1:A100)-SUM(LARGE(A1:A100,ROW(INDIRECT("1:"&D1))))

where D1 contains the "n" value

You must confirm this formula with CTRL+SHIFT+ENTER not just ENTER

**DonkeyOte** · 03-22-2010, 12:43 PM

Or, alternatively:

=SUM(A1:A100)-SUM(INDEX(LARGE(A1:A100,ROW(A1:INDEX(A:A,D1))),0))
confirmed with Enter

**Coume** · 03-22-2010, 01:11 PM

Thanks.

Erm, I also need to do that with Columns. I try transposing Row by Column but obviously, it does not work :/

Any idea?

Thanks
Ludo

**NBVC** · 03-22-2010, 01:18 PM

With my formula, you only need to change the range A1:A100 to your horizontal range... leave the ROW() function as is and confirm with CSE keys.

With DO's formula, you need to change the same range(s) and then change ROW to COLUMN and change the A:A in the index to a row reference, like 2:2

**Coume** · 03-22-2010, 01:35 PM

Originally Posted by NBVC

With my formula, you only need to change the range A1:A100 to your horizontal range... leave the ROW() function as is and confirm with CSE keys.

With DO's formula, you need to change the same range(s) and then change ROW to COLUMN and change the A:A in the index to a row reference, like 2:2

My bad... I did a typo in another part of the formula, while speaking to a customer on the phone and the error in the result was coming from there

Thanks a lot!
Ludo