Count # of non-consecutive cells in a ROW ?

Question

Example is part of a Medical test report recording both individual readings and daily averages of tests.

They're up to 4 a day.

Col L count is the number of readings in Cols D, F, H and J . Need to automate this count (one less potential for error affecting the "average" reading), but COUNTIF requires a range - this isn't one.

If no readings, the time and reading cells are left blank.

Col M is =(Dx+Fx+Hx+Jx)/Col L , (easiest way I know of totalling non-consecutive cells in a row).

A B C D E F G H I J K L M

Answer 1

In Lx:

=COUNT(Dx, Fx, Hx, Jx)

For the average, you can use

=IFERROR(AVERAGE(Dx, Fx, Hx, Jx), "")

or

=IF(Lx=0, "", SUM(Dx, Fx, Hx, Jx)/Lx)

Answer 2

Apologies - I've solved the problem.

(Also, despite spacing Column ID's A - M before submitting, MS in their wisdom doesn't accept space bar as spaces, it concatenated my carefully spaced characters !

In my example, COUNTIF will work. Thinking outside the box -

Select the 8 row cells C - J as a range.

COUNTIF(Cx:Jx "<>") counts the number of C - J cells with data (DOUBLE the number I need, as it's counted both the Date and Reading Columns).

Simple correction - COUNTIF(C1:J1 "<>")/2 halves the number to what is needed.

Only drawback - IF something is inadvertently miskeyed into a cell which should be blank, it gets counted (despite being typed into just one of a two cell block).

If anyone can suggest a way of avoiding that potential count error .... my only thought is, if just one cell had data, the system rounds 0.5's UP to 1. Is it possible to round 0.5's DOWN ?

Answer 3

The formulas that I posted take care of that problem.