Hello everyone!
I am working on speeding up my Excel workflow and stumbled across a big wall.
I do scientific work and calculate the mean with =AVERAGE() for some values.
A B cell line 1 cell line 2 1 20 10 2 30 20 3 cntrl 20 cntrl 15 4 60 empty 5 40 empty
I want to calculate the mean for each cell line, but I have following problems:
- Cannot set a range, because some values do not belong to this cells (marked with cntrl X)
- For cell line 2 the cells B4 and B5 are not filled (no data available)
Formula for AVERAGE I used:
=AVERAGE(A1, A2, A4, A5)
=AVERAGE(B1, B2, B4, B5)
It works fine, but for my experiments I need at least 3 values for proper data, therefore cell line 2 needs at least 1 data field more.
BUT in a sheet auf 400 rows and 300 columns it is hard to spot, where data is missing.
So my question to you is:
Is there a formula/ code which displays how many values excel used to calculate the mean?
To stick with this example the result for:
cell line 1 = 4
cell line 2 = 2 (because 2 empty field are ignored)
OR is
=COUNT(A1, A2, A4, A5) the only way to go?
Thanks in advance!
Best
Phteve
Bookmarks