If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Problem with LARGE function
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 |
#3
|
|||
|
|||
Problem with LARGE function
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 |
#4
|
|||
|
|||
Problem with LARGE function
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 |
Thread Tools | |
Display Modes | |
|
|