I've been following this thread on and off, and it seems our two schools of thought are really at odds. The MUpT (to borrow MrHan's abbreviation, I think it was--although I'm guessing it's been used elsewhere) school are not really interested in getting involved in the nitty-gritty of combat. For them, Civ is an empire-building game on a grand scale, where, as Noddahrassa said:
When I play civ, I want to build an empire, direct the economy and the politics, take care of the broad picture, and not outsmart my opponent in the single battle.
The 1UpT camp, on the other hand, is more interested in detailed combat, where tactics play a much larger role than in MUpT.
Civ4 is my favorite game in the series so far. I have to admit that, yes, there are problems with the stack of doom. I agree that it negates the R/P/S element because once you get enough troops together, you're going to have the counters to anything the enemy might throw at you, and vice versa. So it all comes down to numbers and tech.
But I still enjoy playing Civ4 very much. I don't really mind the SoDs, and I will admit that I get a kick out of putting together a big SoD and steamrolling through enemy territory. Where it starts to break down for me is the endgame, which often feels tedious because there are so many units on the board. Battles are often a foregone conclusion, but you have to go through the tedium of destroying every one of the enemy's troops. I don't get the sense that I am fighting decisive engagements, or that how I move my troops has a significant effect on the outcome of the battle--I just run from one mountain/forest tile to the next until I reach a city. Wipe out the defenders, rinse, and repeat.
So I'm looking forward to Civ5 and a completely new and fresh combat system. I may continue to play Civ4 even as I play Civ5. Maybe some days I just want to put together a SoD and mow down someone. Maybe other days I want to sit down for more detailed combat. In other words, some days I want to wear my president's cap, and other days I want to wear my field commander's cap. I think it will be nice to have that choice.
But, in the end, I agree with Noddahrassa when he said it all comes down to taste. Issues like scale are secondary, if not irrelevant. It really boils down to whether you like your Civ as a bigger picture game or more detailed/tactical in terms of combat. Me, I don't know if I have a preference for one over the other at the moment, but I am definitely looking forward to what Civ5 is going to bring to the table.