0

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.

  • IMPORTREGEX is a custom function but what is its source? Do you have the code or it comes from an add-on? – Rubén Jan 23 at 15:23
  • I think that you should post a new question regarding the "new" problem about having both : 491,"2,480" other wise it's too broad / primarily opinion-based as the solutions could be "fix" your IMPORTREGEX formula, use a formula to "fix" the results of your actual formula, "fix" the code of the custom function. – Rubén Jan 24 at 0:49
0

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....))
0

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.

  • Sorry Rubén, I thought I was editing that into the question! :) – jonsca Jan 24 at 0:32
  • @jonsca No problem – Rubén Jan 24 at 0:35

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.