Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of cells in a column
Hi
How can I write a function Average of cells in a column but not count hiden cells? Normally I write: Average(D:D) but this function will calculate average all cells even hiden cells In my spread sheet I have some hiden cells such as below Cell in row 1 2 3 4 100 101 102 .... i just want to calculate average of cell 1,2,3,4,100,101,102 but skip cells in row 5 to 99 Thanks Daniel |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of cells in a column
what I meant is average of visible cells only.
Average(D:D) will caculate all cells Daniel "Daniel" wrote: Hi How can I write a function Average of cells in a column but not count hiden cells? Normally I write: Average(D:D) but this function will calculate average all cells even hiden cells In my spread sheet I have some hiden cells such as below Cell in row 1 2 3 4 100 101 102 .... i just want to calculate average of cell 1,2,3,4,100,101,102 but skip cells in row 5 to 99 Thanks Daniel |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of cells in a column
Would a user defined function work for you? If so, copy this code into a
regular code module (press [Alt]+[F11] and then Insert | Module and copy and paste the code into the code module presented to you) Public Function AverageOfVisible() As Double Const columnToEvaluate = "D" ' change as necessary Dim lastRow As Long Dim itemCount As Long Dim itemTotal As Double Dim rOffset As Long Dim baseCell As Range Application.Volatile lastRow = Range(columnToEvaluate & _ Rows.Count).End(xlUp).Row itemCount = 0 itemTotal = 0 Set baseCell = Range(columnToEvaluate & "1") For rOffset = 1 To lastRow - 1 ' assumes row 1 is label If baseCell.Offset(rOffset, 0).EntireRow.Hidden = False Then If IsNumeric(baseCell.Offset(rOffset, 0)) Then itemCount = itemCount + 1 itemTotal = itemTotal + baseCell.Offset(rOffset, 0).Value End If End If Next If itemCount 0 Then AverageOfVisible = itemTotal / itemCount Else AverageOfVisible = 0 End If End Function To use the function on a worksheet, just enter this formula: =AverageOfVisible() It could be adapted to work in several different columns by passing a column indicator to the UDF. As written it works in D but that is changeable to any other individual column. "Daniel" wrote: Hi How can I write a function Average of cells in a column but not count hiden cells? Normally I write: Average(D:D) but this function will calculate average all cells even hiden cells In my spread sheet I have some hiden cells such as below Cell in row 1 2 3 4 100 101 102 .... i just want to calculate average of cell 1,2,3,4,100,101,102 but skip cells in row 5 to 99 Thanks Daniel |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of cells in a column
What version of Excel are you using? If 2003 and later you can use
=SUBTOTAL(101,D:D) will only average visible cells in earlier versions you would need a VBA function -- Regards, Peo Sjoblom "Daniel" wrote in message ... what I meant is average of visible cells only. Average(D:D) will caculate all cells Daniel "Daniel" wrote: Hi How can I write a function Average of cells in a column but not count hiden cells? Normally I write: Average(D:D) but this function will calculate average all cells even hiden cells In my spread sheet I have some hiden cells such as below Cell in row 1 2 3 4 100 101 102 .... i just want to calculate average of cell 1,2,3,4,100,101,102 but skip cells in row 5 to 99 Thanks Daniel |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of cells in a column
Hi Peo
Is SUBTOTAL calculate the sum ? but I need to calculate average Thnks Daniel "Peo Sjoblom" wrote: What version of Excel are you using? If 2003 and later you can use =SUBTOTAL(101,D:D) will only average visible cells in earlier versions you would need a VBA function -- Regards, Peo Sjoblom "Daniel" wrote in message ... what I meant is average of visible cells only. Average(D:D) will caculate all cells Daniel "Daniel" wrote: Hi How can I write a function Average of cells in a column but not count hiden cells? Normally I write: Average(D:D) but this function will calculate average all cells even hiden cells In my spread sheet I have some hiden cells such as below Cell in row 1 2 3 4 100 101 102 .... i just want to calculate average of cell 1,2,3,4,100,101,102 but skip cells in row 5 to 99 Thanks Daniel |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of cells in a column
You couldn't even try it before you posted back!!?
Look up SUBTOTAL as a function in help =SUBTOTAL(101,D:D) will average =SUBTOTAL(109,D:D) will sum 102 is COUNT 103 is COUNTA 104 is MAX 105 is MIN -- Regards, Peo Sjoblom "Daniel" wrote in message ... Hi Peo Is SUBTOTAL calculate the sum ? but I need to calculate average Thnks Daniel "Peo Sjoblom" wrote: What version of Excel are you using? If 2003 and later you can use =SUBTOTAL(101,D:D) will only average visible cells in earlier versions you would need a VBA function -- Regards, Peo Sjoblom "Daniel" wrote in message ... what I meant is average of visible cells only. Average(D:D) will caculate all cells Daniel "Daniel" wrote: Hi How can I write a function Average of cells in a column but not count hiden cells? Normally I write: Average(D:D) but this function will calculate average all cells even hiden cells In my spread sheet I have some hiden cells such as below Cell in row 1 2 3 4 100 101 102 .... i just want to calculate average of cell 1,2,3,4,100,101,102 but skip cells in row 5 to 99 Thanks Daniel |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of cells in a column
Thanks all, you saved my life
Daniel "Peo Sjoblom" wrote: You couldn't even try it before you posted back!!? Look up SUBTOTAL as a function in help =SUBTOTAL(101,D:D) will average =SUBTOTAL(109,D:D) will sum 102 is COUNT 103 is COUNTA 104 is MAX 105 is MIN -- Regards, Peo Sjoblom "Daniel" wrote in message ... Hi Peo Is SUBTOTAL calculate the sum ? but I need to calculate average Thnks Daniel "Peo Sjoblom" wrote: What version of Excel are you using? If 2003 and later you can use =SUBTOTAL(101,D:D) will only average visible cells in earlier versions you would need a VBA function -- Regards, Peo Sjoblom "Daniel" wrote in message ... what I meant is average of visible cells only. Average(D:D) will caculate all cells Daniel "Daniel" wrote: Hi How can I write a function Average of cells in a column but not count hiden cells? Normally I write: Average(D:D) but this function will calculate average all cells even hiden cells In my spread sheet I have some hiden cells such as below Cell in row 1 2 3 4 100 101 102 .... i just want to calculate average of cell 1,2,3,4,100,101,102 but skip cells in row 5 to 99 Thanks Daniel |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of cells in a column
Based on your response to other posts, I presume you're using Excel 2003 or
2007, so you can use the SUBTOTAL() offered by them. If it turns out you're using something earlier, then the UDF I offered should do it for you. "JLatham" wrote: Would a user defined function work for you? If so, copy this code into a regular code module (press [Alt]+[F11] and then Insert | Module and copy and paste the code into the code module presented to you) Public Function AverageOfVisible() As Double Const columnToEvaluate = "D" ' change as necessary Dim lastRow As Long Dim itemCount As Long Dim itemTotal As Double Dim rOffset As Long Dim baseCell As Range Application.Volatile lastRow = Range(columnToEvaluate & _ Rows.Count).End(xlUp).Row itemCount = 0 itemTotal = 0 Set baseCell = Range(columnToEvaluate & "1") For rOffset = 1 To lastRow - 1 ' assumes row 1 is label If baseCell.Offset(rOffset, 0).EntireRow.Hidden = False Then If IsNumeric(baseCell.Offset(rOffset, 0)) Then itemCount = itemCount + 1 itemTotal = itemTotal + baseCell.Offset(rOffset, 0).Value End If End If Next If itemCount 0 Then AverageOfVisible = itemTotal / itemCount Else AverageOfVisible = 0 End If End Function To use the function on a worksheet, just enter this formula: =AverageOfVisible() It could be adapted to work in several different columns by passing a column indicator to the UDF. As written it works in D but that is changeable to any other individual column. "Daniel" wrote: Hi How can I write a function Average of cells in a column but not count hiden cells? Normally I write: Average(D:D) but this function will calculate average all cells even hiden cells In my spread sheet I have some hiden cells such as below Cell in row 1 2 3 4 100 101 102 .... i just want to calculate average of cell 1,2,3,4,100,101,102 but skip cells in row 5 to 99 Thanks Daniel |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of cells in a column
If the cells are hidden manually or by filtering and you have XL 2003 you can
use the SUBTOTAL function. =SUBTOTAL(101,A:A) If you have XL 2002 or earlier and rows are hidden by filter use this one =SUBTOTAL(1,A:A) Gord Dibben MS Excel MVP On Wed, 24 Oct 2007 14:40:01 -0700, Daniel wrote: what I meant is average of visible cells only. Average(D:D) will caculate all cells Daniel "Daniel" wrote: Hi How can I write a function Average of cells in a column but not count hiden cells? Normally I write: Average(D:D) but this function will calculate average all cells even hiden cells In my spread sheet I have some hiden cells such as below Cell in row 1 2 3 4 100 101 102 .... i just want to calculate average of cell 1,2,3,4,100,101,102 but skip cells in row 5 to 99 Thanks Daniel |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can I average more cells only in a column by a criteria? | Excel Worksheet Functions | |||
average cells, show 0 if nothing to average | Excel Discussion (Misc queries) | |||
calculate average in a column based on criteria in another column | Excel Discussion (Misc queries) | |||
Sum/average numbers in column A dependant on value in column B | Excel Worksheet Functions | |||
Average the Last Five Cells in a Column | Excel Worksheet Functions |