Average of comma separated numbers from multiple cells

Hi,

If each cell within the range contains just two comma-separated entries, array formula**

=TEXTJOIN(", ",,MMULT(TRANSPOSE(ROW(A39:A40)^0),0+MID(SUBSTITUTE(A39:A40,",",REPT(" ",99)),99*{0,1}+1,99))/2)

which is scalable to larger, single-column ranges, e.g. A1:A100.

Please clarify if there are in fact more than two comma-separated entries and, if so, whether this varies from cell to cell.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Since your second picture appears to be identical to your first, in that the two cells to be queried are A39 and A40, I'm curious as to why you changed the formula I posted. Did you try the formula as it stood?

I have just re-checked and it works fine as it stands, applied to a range of A39:A40.

The 99 has nothing to do with the number of cells being queried, I should point out.

It's true that the formula I posted would require a small amendment to work on ranges which also contain one or more empty cells, but that does not appear to be the case yet.

Regards

Hi and welcome to the forum,
Try this formula:

Formula:

Please Login or Register  to view this content.

Two Cell Average CSV.xlsx

Originally Posted by engineer_infinity

My bad. I will used your formula as is and it worked perfectly. Thank you very much!

Ok, glad to hear it!

As I mentioned, let me know if you need an amendment to account for potential empty cells within the range.

Cheers