You have to make a spell by spell comparison every time you level up
And comparing spells is more art than science.
Let's assume you have 2 enemies, 10 HP each. You do 6 damage to both of them, they still hit you on their rounds. If you do 10 damage to one of them, you will be hit only once. For optimization, 2 x 6 < 10. There is no easy way to measure efficiency for multi-target attacks, but on other boards the following calculation is accepted:
- 100% for the first target
- 50% for the second (1/2)
- 33% for the third (1/3)
- and so on
So a spell that does 12 damage for 4 target is roughly equivalent to a spell that does 12 + 6 + 4 + 3 = 25 to a single target, not 48. And enemies usually do not do you the favor of piling up like this.
Definitely not 11 of them like wax eagle suggests in his answer.
Damage of the slot
Fireball does 8d6 damage for a 3rd level slot, Burning Hand does 5d6. It also has worse reach and smaller area. Fireball wins hands down.
Cone of Cold does 8d8 (average 36), Fireball 10d6 (35) for a 5th level slot. Fireball only does slightly less damage, and still has a better shape. Unless I know I am up against fire resistent creatures, I would still take that one. However for a 9th level slot, Cone of Cold does 12d8 (54) while Fireball does 14d6 (49) damage.
Spell progression is not parallel, if one spell was better in a 2nd level slot, it might not be better at a 5th. Take Burning Hands vs Thunderwave; 1st level slot does 10.5 vs 9 damage, 5th level slot does 24.5 vs 27.
For this reason comparing spells in 9th level slots is only useful if you actually have those.
As wax eagle points out a spell that allowes a save is only worth about 75% of a spell that does not.
Some spells have secondary effects besides direct damage, like the push of Thunderwave. It is hard to put a number on them, but still you have to consider it.
Every time you level up and gain a higher slot, you have to calculate the damage of the spells that just became available for you and compare it to your current spells. If the difference is big, take the new one as one of your free spells.
You have to take into account the campaign setting as well. If the DM only throws kobolds at you, even on 10th level, Fireball is enough. Cone of Cold will not make them any more dead, but it will eat up your 5th level slot.
You have to make this mental arithmetic every time you prepare spells as well.