Top Values in a column - sort of!

[Lee Grant]

Hi there,

I have a column of values (E3:E50) and I want to list below this column, in
a cell (lets say E52) the SUM of the top 5 highest values.

The glitch (as there always is one) is that I don't want every cell to be
considered - I only want, for example, E5, E8, E11, E14, E17, E20, E23, E26,
E29, E32, E35, E38, E41, E44, E47 & E50 to be included.

Is there a way to do this?

Whilst I mention it - would there be a way, with conditional formatting
perhaps, that any cell in that selection (E5, E8, E11, etc.) that IS a top 5
value - could it become a different colour?

Any help would really be appreciated.

Many thanks,

Cheers

Lee

 

[Peo Sjoblom]

1.

=SUM(LARGE(IF((MOD(ROW(E5:E50),3)=2),E5:E50),{1,2,3,4,5}))


entered with ctrl + shift & enter


2.


Select E5:E50 with E5 as the active cell, then use formula is


=AND(MOD(ROW(E5:E5),3)=2,E5>=LARGE(IF((MOD(ROW($E$5:$E$50),3)=2),$E$5:$E$50),5))


click the format button, select pattern and highlight colour and click OK
twice



--


Regards,


Peo Sjoblom

 

[Guest]

For the sum of the 5 largest qualifying numbers,
try this:
=SUMPRODUCT(LARGE(INDEX((MOD(ROW(E3:E50)-2,3)=0)*(E3:E50),0),{1,2,3,4,5}))

The conditioinal formatting becomes a bit complicated if there can be
duplicates.
For example, if every cell in E3:E50 equals 123, then technically they are
ALL match a value in the top 5 and would all be highligted. How would you
want duplicate top-5 values handled....
Match the 1st occurring? the last occurring?

Are duplicates a concern?
***********
Regards,
Ron

XL2003, WinXP

 

[Guest]

Actually....the formula I posted doesn't require SUMPRODUCT to work properly:

so:
=SUM(LARGE(INDEX((MOD(ROW(E3:E50)-2,3)=0)*(E3:E50),0),{1,2,3,4,5}))

Does that help?
***********
Regards,
Ron

XL2003, WinXP

 

[Lee Grant]

Hi Ron,

That works really well - I wish I understood the formula!!

Is there a way to modify the code so that a 'top 5' result can also include
a minus number (for example, the top five results may actually be 1, 4,
5, -7, -3)?

As the for duplicates in the conditional formatting, it doesn't matter if
all 5 results are the same, as long as they are the top five values (so 6,
6, 3, 3, 1 would be as valid as 1, 4, 5, 9, 20 or 8, 8, 8, 8, 8).

That formula is mindblowing - I'll try and deconstruct it to find out how it
does what it does!!

Cheers

Lee

 

[Guest]

Try this:
=SUM(LARGE(INDEX((MOD(ROW(E3:E50)-2,3)=0)*(E3:E50-MIN(E3:E50)),0),{1,2,3,4,5})+MIN(E3:E50))

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP

 

[Lee Grant]

Hi Ron,

Thanks for taking the time to do this. That didn't seem to make a
difference - minus figures are not counted.

I understand that I may not have explained myself too clearly. Would it be
more useful to let you see the actual excel file instead of me trying to
describe it to you? - I've uploaded it to
http://www.inspirationcomputers.com/excel/SRPC.zip In the zip file is
single Excel 2007 file called SRPC.xlsx

Essentially it is a league sheet for our local running club and you'll see
one column for a runner with all the figures.

On the left is a race. Each Race is given a 'Par' - Each runner is given an
'handicap'. The 'target' time for each runner is their Par multiplied by
their handicap.

What I want to be able to do is enter the actual times (time) in the boxes
and display how much quicker or slower (the 'score' value) each runner was
than their 'target' time. This works at the moment.

The bit which you have been very kindly helping me with is trying to
calculate the top 5 'score' times for each runner and display that in the
cell at the bottom of the column.

As you can guess, a top 5 'score' could be a positive number or a negative
number (if a runner completes each race but misses their 'target' times by a
few minutes, they will have a whole board of negative 'score' values - we
would still need their 'top 5' 'score' times however.

A potential pitfall is that not every race will be completed by the runner.
I have setup each race to display '0.00' if only one of the times (target or
time) for each race is present - the theory being that if a runner doesn't
run a race then they won't have a 'time' value and therefore their 'score'
for that race will be 0.00. But I guess that 0.00 could be counted as a
'top 5' result - I guess I could change the formula to leave the cell empty
if the race is run but would that mess up the calculation.

Obviously if you frankly have better things to do on a Saturday than sort
out my dire Excel skills then I cannot say I blame you and I sincerely thank
you for your help.

Cheers

Lee

 

[Guest]

OK...back to your problem.

This ARRAY FORMULA (committed with Ctrl+Shift+Enter, instead of just Enter)
sums the Top 5 non-blank qualifying cells. If there are less than 5 values,
the formula returns "n/a":
=IF(COUNT(IF((MOD(ROW(E3:E50)-2,3)=0)*ISNUMBER(E3:E50),E3:E50))>=5,SUM(LARGE(IF((MOD(ROW(E3:E50)-2,3)=0)*ISNUMBER(E3:E50),E3:E50),{1,2,3,4,5})),"n/a")

Yeah...I know...it's not very pretty.
but, is that somethng you can work with?
(Post back if you have more questions)
***********
Regards,
Ron

XL2003, WinXP

 

[Guest]

This version is a bit shorter:

ARRAY FORMULA:
=IF(SUM((MOD(ROW(E3:E50)-2,3)=0)*ISNUMBER(E3:E50))>=5,SUM(LARGE(IF((MOD(ROW(E3:E50)-2,3)=0)*ISNUMBER(E3:E50),E3:E50),{1,2,3,4,5})),"n/a")

Does that help?
***********
Regards,
Ron

XL2003, WinXP

 

[Lee Grant]

Hi Ron,

Many many thanks - I used the one in the previous post and all seems well.
Thank you so much for your time and energy on this - I don't know how you
worked it out but you did. How does that formula work?

Many, many thanks.

Cheers

Lee

 

[Guest]

Regarding:
=IF(SUM((MOD(ROW(E3:E50)-2,3)=0)*ISNUMBER(E3:E50))>=5,SUM(LARGE(IF((MOD(ROW(E3:E50)-2,3)=0)*ISNUMBER(E3:E50),E3:E50),{1,2,3,4,5})),"n/a")

The IF function section has 3 sections: Test,
Value_to_return_if_test_is_true, Value_to_return_if_test_is_false


Section_1: The test
Contains an expression that returns TRUE/FALSE. If the expression returns a
numeric value, Excel considers 0 to be FALSE and all other numbers to be TRUE.

In our case, we're only interested proceeding if there are at least 5
numeric cells testing every third cell in E3:E50, but beginning with E5.

This part returns TRUE for cells in E3:E50 that match that criteria by
testing if the MOD of the (RowNum_less_2)/3 is zero:
(MOD(ROW(E3:E50)-2,3)=0)

and this expression returns TRUE for each numeric cell:
ISNUMBER(E3:E50)

When the two expressions are multiplied together, each case where the cell's
position qualifies it as valid AND its contents are numeric returns 1. All
other cases return 0. The sum of those products is the count of qualifying
cells with numeric contents.

This complete expression returns that count and checks if it is greater than
or equal to 5:
SUM((MOD(ROW(E3:E50)-2,3)=0)*ISNUMBER(E3:E50))>=5


Section_2: Value_to_return_if_test_is_true
In this section, we use the same construct in the IF function to identify
qualifying cells. If a cell qualifies, its value is returned. If it doesn't
qualify, the IF function returns FALSE: the default returned value when
section_3 of the function is omitted.

Consequently, this expression:
(MOD(ROW(E3:E50)-2,3)=0)*ISNUMBER(E3:E50),E3:E50)

returns an array of numbers, interspersed with FALSE values.

The LARGE function is applied to the returned array. It will ignore FALSE
values and only return numeric values that match the indicated magnitude.
Usually, we only want the 1st or 3rd, etc largest value. But, in our
situation we want the 1st, 2nd, 3rd, 4th and 5th largest values. Hence, the
use of the ARRAY CONSTANT: {1,2,3,4,5}. That causes the LARGE function to
return all 5 values in an array. The enveloping SUM function adds those 5
values together.


Section_3: Value_to_return_if_test_is_false
With our data, if there is less than 5 qualifying values, the formula is set
to return "n/a"


Finally, because we need Excel to treat each expression as an array, we
commit the formula with Ctrl+Shift+Enter.

I hope that helps.
Post back if you have more questions
***********
Regards,
Ron

XL2003, WinXP