how to return 5 largest values from 7 cells?

  • Thread starter Thread starter Svein Erik
  • Start date Start date
Select the five cells in which you want the result to appear.
Then type the following formula:

=LARGE(A1:A7,ROW(INDIRECT("1:7")))

Change A1:A7 to the range containing your 7 value. Then, instead
of pressing Enter, press Ctrl+Shift+Enter. If you do this
correctly, Excel will enclose the formula in curly braces {}.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
=LARGE(A1:A7,ROW(INDIRECT("1:7")))

should be

=LARGE(A1:A7,ROW(INDIRECT("1:5")))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Use the following array formula:

=SUM(LARGE(A1:A7,ROW(INDIRECT("1:5"))))

Change A1:A7 to the appropriate range. Since this is an array
formula, you must press Ctrl+Shift+Enter rather than just Enter
when you enter the formula and whenever you edit it later. If you
do this properly, Excel will enclose the formula in curly braces
{}.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Hi Chip / all,

I wanted your advise on using this same formula to sort a list f
values in ascending order...

what i want to do is, i am trying to make a automated pareto chart
i.e. enter the values and thats it... and currently i have a macro t
sort the list (the item name and frequency) according to th
frequency... so can i use this formula to get a sorted list o
frequencies, or would you suggest something else...
 
Its amazing what you people can !! THANKS
Chip Pearson said:
Use the following array formula:

=SUM(LARGE(A1:A7,ROW(INDIRECT("1:5"))))

Change A1:A7 to the appropriate range. Since this is an array
formula, you must press Ctrl+Shift+Enter rather than just Enter
when you enter the formula and whenever you edit it later. If you
do this properly, Excel will enclose the formula in curly braces
{}.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
Click to expand...
 
This is how I macro-lessly set up Pareto data on worksheet "paretosheet
from a large worksheet called "database". The value I'm evaluating i
in column A of the database.

Row 1 of paretosheet is column headers "value" and "cumulative"

R2C1:
={LARGE('database'!A$2:A$500,ROW()-1)}
(Note that this is an array formula -- don't type the brackets.)

R2C2:
=SUM(A$2:A2)/SUM(A:A)

Fill those two cells down for at least as many rows as are occupied i
database.

The "ROW()-1" in the first formula makes LARGE pick the largest value
then the second largest, etc -- basically sorting the data on the fly.

Note that LARGE chokes if you try to feed it an entire column (i.e.
"A:A" instead of "A$2:A$500"). Not sure why it does that, but it too
me a while to figure out why the forumla wasn't working.

The second formula merely computes the cumulative percentage of th
data.

From here, it's not a big problem to create a pareto chart (discusse
in other tips)
 
Back
Top