[Office 365] Excel VBA: Average range, but ignore cells with test.

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

That said why do you need VBA to do this
On the face of it and without seeing the workbook, standard Excel functions would seem the natural way to obtain the average.

Untested in the absence of a workbook but perhaps

Please Login or Register  to view this content.

Your code does not split comma-delimited entries.

This should do what you want:

Please Login or Register  to view this content.

Please see the attached workbook.

I could not figure out a function-based solution to allow for columns containing comma-delimited values to average properly; it would only average in the last number, and due to organizational needs I can't separate them into helper columns without rewriting other parts of the data.

The VBA I found solved the issue entirely, except for the cells that have text.

Originally Posted by Bernie Deitrick

Your code does not split comma-delimited entries.

The code that I posted calculates the averages properly. I compared them to a manual count using a helper column. It doesn't need to 'split' them, it just needs to calculate them all separately into the average.

Also thank you for your code. I'm testing it to see if it works, and it does ignore the 'no data' entries. Looks like it's working fine for what I needed, thank you very much!

"Splitting" in this context means to separate the numbers using a delimiter, to allow the code to find the numbers used for the average/

Your file with my code and a checking area....