IMPORTHTML in several columns and SUM

Question

I want to calculate the average historical dividend yield of three stocks. For this I import the data from a table with IMPORTHTML.

When I now calculate the sum, the value is 0.

I have played around with VALUE and REGEXTRACT, but unfortunately without success. Can someone give me a hand?

Here is the example in Google Sheet:

https://docs.google.com/spreadsheets/d/1b6vVj0p4vk5R5Zg932manJxpKapkEvdCnifIb0cOmq8/edit#gid=0

Answer 1

The issue is that your values imported by IMPORTHTML are not being interpreted as numbers. To transform them into 'real' percentages, first format the column containing the values as percentages (Using Format/Number/Percent). Then for each imported value you can use:

=regexreplace(index(importhtml("https://aktien.guide/dividende/...";"table";3);4;2);" %";)/100

This strips out the space and percentage characters after each number; the divide by 100 coerces the result into a real number (which is presented as a percentage because of the formatting). Your SUM will then work as expected (although you could just use AVERAGE instead). There are ways of generating the all the values in a single array formula, but in the interests of simplicity I've maintained your original formula constructions.

Answer 2

Text strings that match a number format of your locale can be easily coerced to numbers using a number of approaches.

=VALUE(SUBSTITUTE(<oldFormula>;" ";))

For example,

1. The VALUE function
   • VALUE("1,5") = 1,5
   • 06.05.2023 22:07:37 = VALUE("06.05.2023 22:07:37")
2. Multiply the text string by 1
   • "1,5"*1 = 1,5
   • "06.05.2023 22:07:37"*1 = 06.05.2023 22:07:37

The default % number format on your system is "#0,#%" ("#.##0,#%" would work too), but the format being returned by your formula is "#,#0 %".

In other words, you are only one space " " away from an acceptable format.

My recommendation is to simply

1. Wrap your existing formula in a SUBSTITUTE function, replacing " " with "". You can optionally leave the "replace with" value empty instead of "".
2. Wrap that SUBSTITUTE function in a VALUE function to coerce it to a number

Formula syntax would now be: =VALUE(SUBSTITUTE(<oldFormula>;" ";))

Old Formula

=INDEX(IMPORTHTML("https://aktien.guide/dividende/Air-Liquide-FR0000120073";"TABLE";3);4;2)
="2,29 %"

New Formula

=VALUE(SUBSTITUTE(
   INDEX(IMPORTHTML("https://aktien.guide/dividende/Air-Liquide-FR0000120073";"TABLE";3);4;2);
   " ";))
=2,29%