Adding top 10 numbers from 100
You're welcome!
--
Biff
Microsoft Excel MVP
"Brad" wrote in message
...
Ahhhh! Thank you!!!
"T. Valko" wrote:
I think I may have misunderstood your question!
You use INDIRECT to make it robust against row insertions.
This will work:
ROW(1:10)
However, if you insert new rows above or within the range the formula
will
break. For example, if you inserted a new row 1 the ROW(1:10) becomes
ROW(2:11). Using INDIRECT accounts for row insertions.
ROW(INDIRECT("1:10"))
will *always* refer to 1:10.
--
Biff
Microsoft Excel MVP
"T. Valko" wrote in message
...
What if they wanted to sum the largest 100 numbers out of 1000?
You wouldn't want to do this:
{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,2 0,......100}
So, you do this:
ROW(INDIRECT("1:100"))
--
Biff
Microsoft Excel MVP
"Brad" wrote in message
...
Don't see why the Indirect is needed - seems to work without it....
But
please enlighten me...
--
"Dave Peterson" wrote:
One mo
=SUMPRODUCT(LARGE(A1:H100,ROW(INDIRECT("1:10"))))
Frustrated wrote:
How do I add the top 10 numbers out of 100 numbers that are not in
a
row or
column but scattered throughout a spreadsheet. For instance, the
first
number
might be in cell a2, second in cell ba15, and so on.
How would I write the formula to sum the top something numbers?
--
Dave Peterson
|