Google-sheets – Copying cell values to AVERAGE function

formulasgoogle sheetsimportdata

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.

Best Answer

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....))
Related Topic