I can't stand achievements. You get them for loading the game. I would actually play a game that removes achievements for doing things.
I do agree that some achievements are complete hogwash. When you're playing a game that's deep into story telling and your getting achievements for getting to one part of a story that you could have gotten to by holding one button, it does break the immersion factor by a lot. Knowing this however, some developers have used that to their advantage. Portal 2 for example used a 'story progression' achievement to further sell a joke.
GlaDOS: Well, this is the part where he kills us.
Wheatly: Hello. This is the part where I kill you.
Chapter 9: The Part where he kills you.
Achievement Unlocked: The Part where he kills you.
Now that I thought was funny, not to mention a 'once in a lifetime moment' since you can't get that achievements again' at least on your own account.
And achievements can work. Heck, some games reward achievements like in World of Warcraft. I cannot tell you how I used the achievement system to persuade my friends to get back into the game so we could accomplish these achievements together. Conquer all the criteria for the Wrath of the Lich King expansion and get drake mounts? Sign us up! Those mounts were ours!
Also, some achievements just dare you to do them. I'll never forget my initial reaction when I learned about the 'gnome' achievement in Half Life 2: Episode Two. That's preposterous! The physics don't work like that. There's no where to store it in the car! But I tried it. Oh, god I tried it. It took ALL DAY. Like, really. I was on that game early in the morning all the way to night trying to get that blasted gnome from one side of the Earth to another just so a toothless head crab could have some company on it's way to blowing up a portal. But wow. Did I feel good after accomplishing that feat.
And some of the achievements in RE7 I thought were very interesting. The "Item Managing" achievement I think was the most difficult because you're only allowed to use the item box three times throughout an entire play through of the game. Having beaten the game three times at that point, I knew exactly what this meant. For those who don't know, there are item boxes scattered across the game where you can store items you've picked up for later use. It's helpful because the game only gives you a limited amount of space to store items on your character and you will eventually come across an essential item that you cannot pick up because your inventory is full. Thing is, there are exactly three points in RE7 that require you to use the item box and the one that allows you to unload everything Rambo style happens during the last 10 minutes of the game. So yeah, that was an interesting challenge because it's the only achievement that prohibits you from using all of your unlocked abilities (Better blocking, unlimited ammo, a powerful gun) since they're all stored in the item box at the start of the game.
Here's a hint if you want to do the medical achievement (Don't use more the three medical aids). Use steroids to heal. They not only permanently increase your health, they also fully heal you without counting as a healing aid in the achievement. I literally used zero medical aids on that run because I saved up the steroids.