As with all things, it depends. "Sweet spot" is subjective based on what you're trying to do with the guest VM. The goal is to balance what you're doing in the VM and what you're doing with MacOS so that you don't drive the machine into a excessive paging which reduces performance. Things to think about:.
What is the operating system in the guest? You need to have enough memory allocated to the guest to run it efficiently. For example, I wouldn't run XP with less than 512MB and expect it to do anything useful. Vista will be more, in all likelihood.
What applications are you running in the guest? Use any experience with the applications on a physical machine to gauge how much memory you need to run it effectively.
What applications are you going to run in the Mac OS at the same time? You need enough memory to run them efficiently.
Are your MacOS apps all Intel-native, or are there some PowerPC apps? PPC apps require more memory as they need to be run with MacOS's Rosetta technology
How much memory will your guest OS support?
Oh, and with Fusion 1.1.x, you should not configure a guest with 2GB or greater allocated as USB devices accessed from the guest (i.e. USB sticks and hard drives) will experience problems. This might be fixed in Fusion 2.0.
As a rule of thumb, I would have at least 2GB of memory in my Mac when running Fusion. For 2-3GB of physical memory, I would run with no more than around 50 - 60% of that allocated across all simultaneously running guests. If you have a Mac that can handle more than 3GB of memory, you should be able to allocate more to guests (but note the 2GB issue with Fusion 1.1).