I can throw in my anecdote for opting for a better-than-average tent. I went on a trip years ago where several of us were camped out at a campground. I had a "good" tent, a Sierra Designs one that would be considered low end by gear snobs, but would be considered high end from Walmart shoppers, so I guess it was middle of the road in the scheme of things. Another guy had some sort of Walmart special like a Coleman or a Wenzel. As luck would have it, a huge storm came in during the night, lots of wind and rain. It was causing all sorts of commotion with my rain fly, but everything stayed in place and dry. At one point during the night, I heard what sounded like a car accident, if cars were made out of nylon, accompanied by some colorful language. I shouted out to see if the guy was OK, and he said he was, so I went back to sleep.
The next morning, the storm had passed and I did a check around my tent, and everything looked in place and dry. I went outside to see what the noise was last night, and the first thing I noticed was the guy's rain fly hanging about 10 feet up in a tree 75 yards away. His tent was some jumble of nylon and aluminum on the ground, that looked more like a pile of laundry than a shelter. We surmised that the rain fly, which was "arched" like one of these
, had actually worked as a scoop, which caught a blast of wind, flipped his tent completely upside down, and ripped the fly off. The guy, along with his cooler full of ice, beer and food, were all inside and he just said "fuck it" and dealt with spending the rest of the night soaked by rain and ice water.
I had that Sierra Designs tent for 15 years or so, and it always kept me dry, even when I pitched it in a bad spot where water pooled underneath it. I only recently replaced it because it was aging in places, like one of the zippers stopped working. That's not to say that an inexpensive tent won't work well, as I've seen some cheaper Eurekas and Keltys that are perfectly adequate for under $100, you just need to pay attention to the design of the tent and make sure it'll hold up in all conditions.
In addition to the places listed for good deals, keep an eye on steepandcheap.com as good deals can be had on there. I picked up a nice Mountain Hardware 3 person tent for ~$130 last year, and its been superb so far.