I used it once in an accounting situation.

The cube had one base measure that held the value of each of the
accounts. In the first month of the year there was an account called
"Retained Earnings" which was mean to display the net profit (or loss)
from the prior year. I used a Calculated Cell to override the measures
value when the user was looking at the retained earnings account member
for the first month in the year.

I could have achieved the same thing through creating a calculated
measure with an iif statement. (you can even set the base measure to
being not visible if you want)

But I can't think off the top of my head of any situations where
calculated cells could do something that could not be done using a
calculated member.

There was a thread running in the microsoft.public.sqlserver.olap
newsgroup called "Calculated Member solution (Dave, Deepak)" which might
give you another example.