In column A I have a list of names. Columns B,C,D have AVG,SUM,COUNT. COUNT and SUM get their values from column E,F,G,H and so on. Column B calculates the AVG from SUM and COUNT. From time to time I add info to the sheet which includes names that already exist in column A and add new names if they don't exist in column A. I would like to dynamically add the sum,avg,count to B,C,D as the sheet is updated. I know this can be done with a cell formula but I don't want cells to show #DIV/0! or zeros if there is no information for them. Can this be done with VBA instead? here is sample vba:
For y = 8 To last_cell
If player_name = Worksheets("Current Year").Cells(y, 1) Then
Worksheets("Current Year").Cells(y, 5).Value = position
' what i would like to calculate
Worksheets("Current Year").Cells(y, 2).Value = AVG
Worksheets("Current Year").Cells(y, 3).Value = SUM
Worksheets("Current Year").Cells(y, 4).Value = COUNT of range ' E "y" ' to so on
player_exists = True
End If
it can be done with formula and without error like #DIV/0
cellAVG = =IF( ISERROR(AVERAGE(E1:H1)),0,
cellSUM= =SUM(E1:H1)
cell Count= =COUNTIF(E1:H1;">0")
instead of error