SumProduct of nth Largest over non contiguos cells

[John]

I need to sum the product of a number of cells based on a list of
values over cells that are not contigous.

I also want to be able to have a single cell that will have a value
entered to decide what the nth value will be

For example, the 'Values' are in
A2, C2, E2, F2, J2, L2, N2, S2
In Cell A1 I might enter the value of 5. This will mean I want the
total of the 5 largest values.


Regards
John

 

[Peo Sjoblom]

Select all your cells (select first cell, hold down ctrl and click each
cell), then click in the name box and name the range
assume you name the range MyLst, now use

=SUMPRODUCT(LARGE(MyLst,ROW(INDIRECT("1:"&A1))))

replace MyLst with whatever you name the range

--


No private emails please, for everyone's
benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Ron Rosenfeld]

I need to sum the product of a number of cells based on a list of
values over cells that are not contigous.

I also want to be able to have a single cell that will have a value
entered to decide what the nth value will be

For example, the 'Values' are in
A2, C2, E2, F2, J2, L2, N2, S2
In Cell A1 I might enter the value of 5. This will mean I want the
total of the 5 largest values.

Here's one way.

Define a NAME (rng) which is the non-contiguous range.

Then this *array-entered* formula will give you the SUM of the n largest
numbers:

=SUM(LARGE(rng,ROW(INDIRECT("1:"&A1))))

To get the product of those cells, use the array-entered formula:

=PRODUCT(LARGE(rng,ROW(INDIRECT("1:"&A1))))

To *array-enter* a formula, hold down <ctrl><shift> while hitting <enter>. XL
will place braces {...} around the formula.


--ron

 

[JohnT]

Peo

Absolutely fantastic response time. I'm not sure what time zone you'r
in but I don't think you sleep.

I wasn't aware that you could name non contigous cells. Now I do, thi
works great. However, can you explain the

ROW(INDIRECT("1

part of the formula please.

John

 

[JohnT]

Ron

Peo beat you to it.
But what is the difference between sum & product??

John

 

[Ron Rosenfeld]

Ron

Peo beat you to it.

When I try his formula, I get the SUM of the values. Is that what is wanted?
I'm not sure. The OP used the term "sum the product of a number of cells".

But what is the difference between sum & product??

SUM is when you add the numbers; PRODUCT is when you multiply them all
together.

When the OP posted, I was not clear if he wanted the SUM or the PRODUCT of the
values.


--ron

 

[JohnT]

SUMPRODUCT is the result I want, (I think) Add up the 5 larges
numbers.

Joh

 

[Peo Sjoblom]

JohnT

=LARGE(Range,1)

as an example will return the largest number, however

=LARGE(Range,{1;2;3;4;5})

will return an array of the 5 largest numbers (put in one cell it will
return the same as the first formula
however if you highlight it, and press F9 it will look like)

={1st;2nd;3rd largest and so on}

now wrap that in either sum or sumproduct the array will be summed

You wanted the be able to set how many largest number you wanted to total by
using A1
One way of doing that is to use row and indirect by combining text
expression "1:" and numbers (5 in A1)

"1:" &A1

the rowindirect part returns an array (using 5 in A1 it would look like
{1;2;3;4;5})
select the ROW(INDIRECT("1:"&A1)) part in the formula bar, press F9 and
you'll see that
it will be the same as in the second formula.

--


No private emails please, for everyone's
benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Ron Rosenfeld]

SUMPRODUCT is the result I want, (I think) Add up the 5 largest
numbers.

John

Well then, my array-entered SUM formula, and Peo's non-array-entered SUMPRODUCT
formulas return the same results.


--ron

 

[JohnT]

Thanx Peo & Ron

Especially Peo for the explanation of the formula

Regards
Joh