Looking to for a solution to completely replace the AVERAGE function.
New formula needs to handle blanks and texts as not to use them in the division for the average.
thoughts have been SUMMPRODUCT with -- for count and also trying to use COLUMNS - COUNTA to modify the count for division.
getting stuck putting it all together.. assistance in completing this creative solution would be appreciated.
example input cells to average
=B23 0.85 =C23.84 0.95 =D23 rrr
=AVERAGE($B$23:H23)
works for blanks but not inbetween blanks
=SUM($B$23:H23)/(((SUMPRODUCT(--($B$23:H23<>"")))-((COLUMNS($B$23:H23)-(COUNTA($B$23:H23))))))
will not handle any blanks
=SUM($B$23:E23)/(((SUMPRODUCT(--($B$23:E23<>"")))-((COLUMNS($B$23:E23)-(COUNTA($B$23:E23))))))
New formula needs to handle blanks and texts as not to use them in the division for the average.
thoughts have been SUMMPRODUCT with -- for count and also trying to use COLUMNS - COUNTA to modify the count for division.
getting stuck putting it all together.. assistance in completing this creative solution would be appreciated.
example input cells to average
=B23 0.85 =C23.84 0.95 =D23 rrr
=AVERAGE($B$23:H23)
works for blanks but not inbetween blanks
=SUM($B$23:H23)/(((SUMPRODUCT(--($B$23:H23<>"")))-((COLUMNS($B$23:H23)-(COUNTA($B$23:H23))))))
will not handle any blanks
=SUM($B$23:E23)/(((SUMPRODUCT(--($B$23:E23<>"")))-((COLUMNS($B$23:E23)-(COUNTA($B$23:E23))))))