Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula help needed for averages
Or, if you prefer an "unsimple" approach, here's some code that'll do
the same thing (just pass a reference to your range into it): Public Function AverageTheCells(objRange As Range) Dim nCell As Integer Dim lTotal As Long Dim nDenominator As Integer Dim lAverage As Long For nCell = 1 To objRange.Cells.Count If objRange.Cells(nCell, 1).Value 0 Then nDenominator = nDenominator + 1 lTotal = lTotal + objRange.Cells(nCell, 1).Value End If Next nCell If nDenominator 0 Then AverageTheCells = lTotal / nDenominator End Function Alok wrote: Use countif =COUNTIF(A1:A5,"0") "phil-rge-ee" wrote: I have a cells C21 thru C45 that have numbers in them. I need to add them all together and then divide by the number of cells that have a number greater than 0 in them. This would be used to get an average number. If the cell is 0 then I don't want to include it in the dividing process. Example: C21 is 5 C22 is 3 C23 is 0 C24 is 7 C25 is 0 I would need the formula or VB script to add the numbers then divide by 3 (not divide by 5 - eliminating the cells with 0 in them) So the answer in the example here would be 5, not 3. (15 divided by 3). Thanks for any help. Phil |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula for averages | Excel Discussion (Misc queries) | |||
help with averages formula | Excel Discussion (Misc queries) | |||
help with averages formula | Excel Discussion (Misc queries) | |||
formula help needed for averages | Excel Programming | |||
averages formula | Excel Worksheet Functions |