Hi All

Im having trouble making a formula that averages a sum by the number of cells that contain data
I.e
I have 12 cells, on a row. (A1 to A12). In A13 i have a total of A1 to A12
In A13 i need to average the total by the number of cells in A1-A12 that contain data
i know i can have a cell that does the count, and then another cell that divides the total by the cell count, but that make this messy on the spreadsheet. (Used for financial reporting.

Im not sure on an appropriate formula.
Could someone possibly tell me a formula/function that i can use for the above

Thanks heaps

Toby.

Toby

one way:

=SUM(A1:A12)/COUNTIF(A1:A12,"<")

Regards

Trevor


"Toby T" wrote in message
...
Hi All.

Im having trouble making a formula that averages a sum by the number of
cells that contain data.
I.e.
I have 12 cells, on a row. (A1 to A12). In A13 i have a total of A1 to
A12.
In A13 i need to average the total by the number of cells in A1-A12 that
contain data.
i know i can have a cell that does the count, and then another cell that
divides the total by the cell count, but that make this messy on the
spreadsheet. (Used for financial reporting.)

Im not sure on an appropriate formula.
Could someone possibly tell me a formula/function that i can use for the
above.

Thanks heaps.

Toby.

Trevors suggestion is probably the best one but if you want a function this
does it.....

Public Function NZAvg(NZR As Range)
Dim nzcount As Long, nzsum As Double, cell As Variant
nzcount = 0
For Each cell In NZR
If Val(cell.Value) 0 Then
nzcount = nzcount + 1
nzsum = nzsum + cell.Value
End If
Next
If nzcount 0 Then
NZAvg = nzsum / nzcount
Else
NZAvg = CVErr(xlErrValue)
End If
End Function


put the formula =NZAvg(A1:A12) in the worksheet cell to calculate the
non-zero average for the range.

Cheers
Nigel

"Toby T" wrote in message
...
Hi All.

Im having trouble making a formula that averages a sum by the number of
cells that contain data.
I.e.
I have 12 cells, on a row. (A1 to A12). In A13 i have a total of A1 to
A12.
In A13 i need to average the total by the number of cells in A1-A12 that
contain data.
i know i can have a cell that does the count, and then another cell that
divides the total by the cell count, but that make this messy on the
spreadsheet. (Used for financial reporting.)

Im not sure on an appropriate formula.
Could someone possibly tell me a formula/function that i can use for the
above.

Thanks heaps.

Toby.

Thanks heaps Trevor
That works great.