Show Top 3 Values
 

Hello,

I have a worksheet with several records(rows) and each record contains
several columns (5) with values. What I would like to do is to display the
top 3 values from that record. How would I set that up?

Sample Data:
For Row 1
ColA: 100
ColB: 150
ColC: 250
ColD:115
ColE:250

Top 3 Values would be from the list above would be: 250, 250, and 150. I
don't want to manually do this for 1500 records. Is there a way to write a
function for this?

Thanks in Advance.

Show Top 3 Values
 

have a look in the help index for LARGE and RANK

--
Don Guillett
SalesAid Software

"Bagia" wrote in message
...
Hello,

I have a worksheet with several records(rows) and each record contains
several columns (5) with values. What I would like to do is to display the
top 3 values from that record. How would I set that up?

Sample Data:
For Row 1
ColA: 100
ColB: 150
ColC: 250
ColD:115
ColE:250

Top 3 Values would be from the list above would be: 250, 250, and 150. I
don't want to manually do this for 1500 records. Is there a way to write a
function for this?

Thanks in Advance.

Show Top 3 Values
 

In column F

=LARGE($A1:$E1,1)

in G

=LARGE($A1:$E1,2)

in H

=LARGE($A1:$E1,3)

HTH

"Bagia" wrote:

Hello,

I have a worksheet with several records(rows) and each record contains
several columns (5) with values. What I would like to do is to display the
top 3 values from that record. How would I set that up?

Sample Data:
For Row 1
ColA: 100
ColB: 150
ColC: 250
ColD:115
ColE:250

Top 3 Values would be from the list above would be: 250, 250, and 150. I
don't want to manually do this for 1500 records. Is there a way to write a
function for this?

Thanks in Advance.

Show Top 3 Values
 

That works well Toppers. I'm going to take a step further and wanted to see
if I can have a function that will sum up the top 3 values from the row. Is
there a function for that?

So instead of creating a function for each column to get highest, 2nd
highest, and 3rd highest, just have the system sum up the to three values in
one column say column F?

Thanks in advance.

"Toppers" wrote:

In column F

=LARGE($A1:$E1,1)

in G

=LARGE($A1:$E1,2)

in H

=LARGE($A1:$E1,3)

HTH

"Bagia" wrote:

Hello,

I have a worksheet with several records(rows) and each record contains
several columns (5) with values. What I would like to do is to display the
top 3 values from that record. How would I set that up?

Sample Data:
For Row 1
ColA: 100
ColB: 150
ColC: 250
ColD:115
ColE:250

Top 3 Values would be from the list above would be: 250, 250, and 150. I
don't want to manually do this for 1500 records. Is there a way to write a
function for this?

Thanks in Advance.

Show Top 3 Values
 

=SUM(LARGE($A$1:$E$1,1),LARGE($A$1:$E$1,2),LARGE($ A$1:$E$1,3))

"Bagia" wrote:

That works well Toppers. I'm going to take a step further and wanted to see
if I can have a function that will sum up the top 3 values from the row. Is
there a function for that?

So instead of creating a function for each column to get highest, 2nd
highest, and 3rd highest, just have the system sum up the to three values in
one column say column F?

Thanks in advance.

"Toppers" wrote:

In column F

=LARGE($A1:$E1,1)

in G

=LARGE($A1:$E1,2)

in H

=LARGE($A1:$E1,3)

HTH

"Bagia" wrote:

Hello,

I have a worksheet with several records(rows) and each record contains
several columns (5) with values. What I would like to do is to display the
top 3 values from that record. How would I set that up?

Sample Data:
For Row 1
ColA: 100
ColB: 150
ColC: 250
ColD:115
ColE:250

Top 3 Values would be from the list above would be: 250, 250, and 150. I
don't want to manually do this for 1500 records. Is there a way to write a
function for this?

Thanks in Advance.

Show Top 3 Values
 

=SUM(LARGE(A1:E1,{1,2,3}))

Or, say you wanted to sum the largest 50 from column A:

Array entered:

=SUM(LARGE(A:A,ROW(INDIRECT("1:50"))))

Biff

"Toppers" wrote in message
...

=SUM(LARGE($A$1:$E$1,1),LARGE($A$1:$E$1,2),LARGE($ A$1:$E$1,3))

"Bagia" wrote:

That works well Toppers. I'm going to take a step further and wanted to
see
if I can have a function that will sum up the top 3 values from the row.
Is
there a function for that?

So instead of creating a function for each column to get highest, 2nd
highest, and 3rd highest, just have the system sum up the to three values
in
one column say column F?

Thanks in advance.

"Toppers" wrote:

In column F

=LARGE($A1:$E1,1)

in G

=LARGE($A1:$E1,2)

in H

=LARGE($A1:$E1,3)

HTH

"Bagia" wrote:

Hello,

I have a worksheet with several records(rows) and each record
contains
several columns (5) with values. What I would like to do is to
display the
top 3 values from that record. How would I set that up?

Sample Data:
For Row 1
ColA: 100
ColB: 150
ColC: 250
ColD:115
ColE:250

Top 3 Values would be from the list above would be: 250, 250, and
150. I
don't want to manually do this for 1500 records. Is there a way to
write a
function for this?

Thanks in Advance.

Show Top 3 Values
 

Thanks to everyone for their suggestions. The formula below works perfectly.
"T. Valko" wrote:

=SUM(LARGE(A1:E1,{1,2,3}))

Or, say you wanted to sum the largest 50 from column A:

Array entered:

=SUM(LARGE(A:A,ROW(INDIRECT("1:50"))))

Biff

"Toppers" wrote in message
...

=SUM(LARGE($A$1:$E$1,1),LARGE($A$1:$E$1,2),LARGE($ A$1:$E$1,3))

"Bagia" wrote:

That works well Toppers. I'm going to take a step further and wanted to
see
if I can have a function that will sum up the top 3 values from the row.
Is
there a function for that?

So instead of creating a function for each column to get highest, 2nd
highest, and 3rd highest, just have the system sum up the to three values
in
one column say column F?

Thanks in advance.

"Toppers" wrote:

In column F

=LARGE($A1:$E1,1)

in G

=LARGE($A1:$E1,2)

in H

=LARGE($A1:$E1,3)

HTH

"Bagia" wrote:

Hello,

I have a worksheet with several records(rows) and each record
contains
several columns (5) with values. What I would like to do is to
display the
top 3 values from that record. How would I set that up?

Sample Data:
For Row 1
ColA: 100
ColB: 150
ColC: 250
ColD:115
ColE:250

Top 3 Values would be from the list above would be: 250, 250, and
150. I
don't want to manually do this for 1500 records. Is there a way to
write a
function for this?

Thanks in Advance.