Cell populated with math equation with results in adjacent cell?

Hey Excel Forum.

I am a weightlifting coach, that has an IT background of all things. I write our training programs using excel so I can take advantage of tallying up the workload of my athletes, graphing and charting as well.

I have a question about something that on it's surface seems very simple, but I also understand why it doesn't work. If I have a cell with the following text "1+1+2" I would like to determine a way to total that in another cell (eg. "4").

This is useful because there are some instances where an athlete will perform multiple exercises in sequence (we call them complexes) and I have to calculate volume for the entire complex (repetitions * kilograms). If I have an athlete performing three exercises in sequence and the last exercise has 2 repetitions when the first two have only 1 repetition it will be written out as "1+1+2" in the repetitions field. To determine the difficulty of this exercise I now have to take the sum of those repetitions and multiply it by the weight lifted.

Using multiple cells would make formatting extremely difficult which is why it's not something I'd like to consider. Some complexes may only have 2 exercises whereas some will have upwards of 3 or 4. This is why splitting the cells up would not be optimal.


tl;dr - I'm wondering if it's possible to have a cell populated with a simple mathematics equation and publish those results to a different cell.

Here's one way round it - it's a bit topsy turvy, though!

In the cell where you want the total to appear, type:

=1+1+2

In the cell where you want the formula to appear, type:

=FORMULATEXT(A1)

where A1 is the text you populated with the sum.

EDIT: Sorry - was posting at the same time as the mod.

It is a bit topsy turvy, but I can work with that. Thanks.

You're welcome!