I think some of the worst failures are mediocre projects that go sort-of okay and therefore continue to eat up talent for a much longer time than needed; cases where ambitious projects fail to “fail fast”. It takes a lot of judgment ability and self-honesty to tell that it’s a failure relative to what one could have worked on otherwise.
One example is Raising for Effective Giving, a poker fundraising project that I helped found and run. It showed a lot of promise in terms of $ raised per $ spent over the years it was operating, and actually raised $25m for EA charities. But it looks a lot less high-impact once you draw comparisons to GWWC and Longview, or once you account for the small market size of the poker industry, lack of scalability, the expected future funding inflows into EA, and compensation from top Earning To Give opportunities. $25 million is really not much compared to the billions others raised through billionaire fundraising and entrepreneurship.
I personally failed to admit to myself that the project was showing mediocre but not amazing results, and only my successor (Stefan) then discontinued the project, which in hindsight seems like the correct judgment call.