=AVERAGE display amount of values used

[phteve]

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

[Glenn Kennedy]

One way:

=COUNTIFS(B2:B6,"<>",B2:B6,"<>cntrl*")

You may need ; instead of ,

[phteve]

Thanks!

<> means, that values = 0 are not counted?


=COUNT(B1, B2, B4, B5) should also work, or?

[Glenn Kennedy]

It means anything other than bblank. Your suggestion would be a pain to maintain (totally manual). Did you try it yourself? Did it work for you? I think we need to see a bigger dataset. What is below row 5? This can probaly be hugely automated.

[phteve]

Thanks!


Unfortunately I cannot share the data (is confidential), but I can explain it:

49 columns
222 rows

compound	concentration	product 1	product 2
1	1	345	123
1	10	324	213
control		32	43
2	1	433	156
2	10	366	210
2	1	345	123
2	10	524	313
control		44	53
1	1	403	111
1	10	336	211

and I have to calculate the mean for each compound + concetration + product (cpd 1 at concentration 1 for product 1, cpd 1 at concetration 10 for product...)

Unfortunately there is no scheme which values should be used (other data)

So I did each formula manually...

=AVERAGE(C$2, C$10) than dragged this formula to the end for this row

Next row same, I manually selected the cells.


Than I copied all formulas below (otherwise my column number would be altered) and manually changed "AVERAGE" to "COUNT".

Took me a while, and I actually don't want to do this again

[Glenn Kennedy]

1. I did not ask to see your real sheet. A mocked up sample is fine.

2. In your initial post you were talking about a minimum of n=3 per product. Now it look slike n=1. Explain.

Please mock up a more comprehensive sample that matches what you REALLY want and what you REALLY have. Post as an Excel sheet. See yellow banner for instructions on posting a SHEET. Also show manually calculated results.

[phteve]

1. I did not ask to see your real sheet. A mocked up sample is fine.

2. In your initial post you were talking about a minimum of n=3 per product. Now it look slike n=1. Explain.

Please mock up a more comprehensive sample that matches what you REALLY want and what you REALLY have. Post as an Excel sheet. See yellow banner for instructions on posting a SHEET. Also show manually calculated results.

Ahh oke,
for sure, I can prepare a sample file

[phteve]

I just did it for 5 products (instead of 49)
and only for a set of compounds

I always have to compare the same compound at the same concetration in M1 or M2 (but not mixed)


Thanks!

[Glenn Kennedy]

Better!!

N3, copied across and down:
=AVERAGEIFS(E:E,$C:$C,$M$3,$A:$A,$K3,$B:$B,$L3)

N12, copied across and dnown:
=COUNTIFS($A:$A,$K12,$B:$B,$L12,$C:$C,$M$12,E:E,"<>")

That'll speed you up. i never liked DMSO. I really dislike its smell.

[phteve]

Thanks you so much!


Unfortunately it dissolve almost everything we need to

[Glenn Kennedy]

N-methyl pyrrolidinone ? Dimethylacetamide?? I used to have to use pyridine a lot, for semi-organic solutions of proteins. It made me hugely unpopular with everyone else.

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[phteve]

I got my compounds dissolved in DSMO and cannot change the solvent for my negative controls.
But for sure, I will change the solvent for my next samples...

[Glenn Kennedy]

DMSO is particularly unpleasant. Ever got any on your skin? You can taste it a few seconds later...

Glenn

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[phteve]

not yet, I give my best to avoid that