I would not base my opinion on Retro tests. I do have high confidence in the on-demand tests.
However, the most important criterion of any software is whether or not it performs well on your system. If it does not, and gives trouble I dump it and find one that does.
Avast does not show as well as some others. However, that fact does not mean it is not good. I tried Avira in all versions, and while it is lighter than Avast, and has better detection rates, it caused me some problems that I was not willing to tolerate. Of the two I prefer Avast for my use.
For probably at least 3 years I have recommended Avast Home to people who are looking for a good AV. Two of those are my daughter, and one of my best friends.
None of the folks that have installed Avast has had any problems with infections, and Avast seems to run well on most machines. I do not hesitate to recommend it to those who I think are not dangerous surfers.
Although I do not currently run Avast, it has always been my "fall back" AV.
I do have high confidence in AV Comparatives, and any AV that has been rated Advanced, it a good one.
The retro tests use, if I remember correctly, three month old samples, and the AVs are not updated for that 3 month period. That test heuristics, but ignores signature updates, and Proactive Defense such as KAV. Although IBK has forgotten more that I'll ever know about computers, and security I do not put much stock in the retro tests. I had rather trust fast updates and the Proactive Defense of KAV.
Avast is an excellent AV, and I would predict that if one uses it and is a wise user, he will never experience security problems.