Problem with LARGE function

I am trying to sum the best 2 scores from a total of 4 scores using the LARGE
function. the 4 scores are immediately above the cell containing the
function. I can do the function correctly if there are no hidden rows but it
doesn't work if there are hidden rows within the range that the function is
looking over.

The funtion I have is =SUM(Large(A1:A4,ROW(INDIRECT("1:"&C1))))

in cell C1 i have 2.

This works if there are no hidden rows, if I insert a row and then hide this
row the function does work but picks up the value in the hidden row if that
value is recognzed as 1 of the 2 largest values. I do not want any values
in the hidden rows to be included in the SUM.

Thanks

Hi,

How about a User Defined function

Alt+F11 to open VB editor. Right click 'ThisWorkbook' and insert module and
paste the code below in. Call it with

=SumLargeVisible(A1:A100,3)

It would sum the 3 largest visible numbers so change that to suit.


Function SumLargeVisible(rng As Range, lg As Long)
Dim NewRange As Range, c As Range
Dim x as long
For Each c In rng
If c.RowHeight 0 Then
If NewRange Is Nothing Then
Set NewRange = c
Else
Set NewRange = Union(NewRange, c)
End If
End If
Next
For x = 1 To lg
SumLargeVisible = SumLargeVisible + _
WorksheetFunction.Large(NewRange, x)
Next
End Function
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"mickjjuk" wrote:

I am trying to sum the best 2 scores from a total of 4 scores using the LARGE
function. the 4 scores are immediately above the cell containing the
function. I can do the function correctly if there are no hidden rows but it
doesn't work if there are hidden rows within the range that the function is
looking over.

The funtion I have is =SUM(Large(A1:A4,ROW(INDIRECT("1:"&C1))))

in cell C1 i have 2.

This works if there are no hidden rows, if I insert a row and then hide this
row the function does work but picks up the value in the hidden row if that
value is recognzed as 1 of the 2 largest values. I do not want any values
in the hidden rows to be included in the SUM.

Thanks

Hi Mike

Thanks for the answer, I can see how this works. I'm using it on a golf
stableford scoring worksheet and so this function would have to be run for
each and every set of scores for each individual player in a tournament.
There may be many players (upto 200) so it's probably a little 'overkill' for
what I'm trying to achieve. I may have to re-design the worksheet moving the
hidden row elements.

Many thanks for your help.

"Mike H" wrote:

Hi,

How about a User Defined function

Alt+F11 to open VB editor. Right click 'ThisWorkbook' and insert module and
paste the code below in. Call it with

=SumLargeVisible(A1:A100,3)

It would sum the 3 largest visible numbers so change that to suit.


Function SumLargeVisible(rng As Range, lg As Long)
Dim NewRange As Range, c As Range
Dim x as long
For Each c In rng
If c.RowHeight 0 Then
If NewRange Is Nothing Then
Set NewRange = c
Else
Set NewRange = Union(NewRange, c)
End If
End If
Next
For x = 1 To lg
SumLargeVisible = SumLargeVisible + _
WorksheetFunction.Large(NewRange, x)
Next
End Function
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"mickjjuk" wrote:

I am trying to sum the best 2 scores from a total of 4 scores using the LARGE
function. the 4 scores are immediately above the cell containing the
function. I can do the function correctly if there are no hidden rows but it
doesn't work if there are hidden rows within the range that the function is
looking over.

The funtion I have is =SUM(Large(A1:A4,ROW(INDIRECT("1:"&C1))))

in cell C1 i have 2.

This works if there are no hidden rows, if I insert a row and then hide this
row the function does work but picks up the value in the hidden row if that
value is recognzed as 1 of the 2 largest values. I do not want any values
in the hidden rows to be included in the SUM.

Thanks

Hi,

Try this array formula (Ctrl+Shift+Enter). Data is in A2:A5. A1 has the
heading. C 1 has 2

=SUM(LARGE((SUBTOTAL(109,OFFSET(A1,ROW(A2:A5)-ROW($A$1),))),ROW(INDIRECT("1:"&C1))))

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"mickjjuk" wrote in message
news
I am trying to sum the best 2 scores from a total of 4 scores using the
LARGE
function. the 4 scores are immediately above the cell containing the
function. I can do the function correctly if there are no hidden rows but
it
doesn't work if there are hidden rows within the range that the function
is
looking over.

The funtion I have is =SUM(Large(A1:A4,ROW(INDIRECT("1:"&C1))))

in cell C1 i have 2.

This works if there are no hidden rows, if I insert a row and then hide
this
row the function does work but picks up the value in the hidden row if
that
value is recognzed as 1 of the 2 largest values. I do not want any
values
in the hidden rows to be included in the SUM.

Thanks