Copying cell values to AVERAGE function

Question

I’m pulling Free Cash Flow Data from Morningstar to Google Spreadsheet.

I have formula:

=IMPORTREGEX("http://financials.morningstar.com/ajax/exportKR2CSV.html?&callback=?&t="&B4,"Free Cash Flow ... Mil,(.*)\s")

In column B there are more than 100 tickers. I get the data in this form to column C (US, and . System)

"8,892","9,165","8,905","10,365","11,734","11,057","11,325","12,424","12,912","12,832","13,471"

I want to calculate the average of these free cash flow numbers. I made a macro that copies the values from column C to column D. In column E I tried to calculate the average, but it couldn’t get it to work.

=AVERAGE(D4)

Result #DIV/0!

If I do manually:

=AVERAGE("8,892","9,165","8,905","10,365","11,734","11,057","11,325","12,424","12,912","12,832","13,471")

Result 11189.27273, it works.

I have more than 100 rows where I want to do the same, so I would prefer a quicker way.

Any ideas how to get the values to the AVERAGE function and use it in multiple rows?

---

The importregex code is from here:

https://stackoverflow.com/questions/39014766/to-exceed-the-importxml-limit-on-google-spreadsheet

I tried

=AVERAGE(SPLIT(SUBSTITUTE(SUBSTITUTE(D4,""",""","""|"""),"""",""),"|")) works with those companies which have only numbers like "8,892".

=AVERAGE(SPLIT(SUBSTITUTE(SUBSTITUTE(D4,",","|"),"""",""),"|")) works with those companies which have only numbers like 892.

Some tickers have both : 491,"2,480" which is a problem.

Answer 1

Not sure what your custom =IMPORTREGEX() formula does, but AVERAGE() formula can work only with numeric values (eg. if a cell is formatted as text, then "average of text" can't be calculated) and most likely data which is imported into your spreadsheet is imported as text data, therefore you will need to convert this dataset into numbers. You can do so with VALUE() and for continuity use ARRAYFORMULA() eg.:

=ARRAYFORMULA(VALUE(import....))

Answer 2

The problem is that AVERAGE(D4) it's not seen D4 value as

"8,892","9,165","8,905","10,365","11,734","11,057","11,325","12,424","12,912","12,832","13,471"

it's seen it as

"""8,892"",""9,165"",""8,905"",""10,365"",""11,734"",""11,057"",""11,325"",""12,424"",""12,912"",""12,832"",""13,471"""

WORKAROUND

=AVERAGE(SPLIT(SUBSTITUTE(SUBSTITUTE(D4,""",""","""|"""),"""",""),"|"))

Explanation

It looks that IMPORTREGEX returns a TEXT that use commas as thousands separator and as value separator, so first, we replace the comma used as value separator by another character then, convert the text into an array, then calculate the average.

NOTE: Since IMPORTREGEX is a custom function maybe it could be tweaked to return an array instead of a string.