How do you calculate figures held in multiple cells?

**SteveBIM** · 03-26-2015, 04:11 PM

I've got a, hopefully, easy to solve problem.

The attachments shows you what I want to do, but I'm at a loss how to achieve the result.

The aim is to record the arbitrage outcomes of various sports matches.

For each match there are three possible outcomes: 1 = a home win, x = a draw, and 2 = an away win.

Each has it's own individual odds, stakes, and profit/loss.

What I'd like to do is calculate the values for the 'P/L £' column using the data held in the Win/Lose, Odds, and Stake cells. Then to calculate the overall P/L from the P/L £ cell (I've filled those in manually on the example).

I know how to do the formula - =IF(F6="W",H6*(G6-1),0-H6) and so on - but what I'm stuck on is how to apply that to each line in the multiple cells.

I set the multiple cells up by doing Alt+Enter. Perhaps this is where the problem lies and there's possibly a simple solution to grouping these outcomes together.

Many thanks,

Steve.

**Bernie Deitrick** · 03-26-2015, 04:45 PM

Using Alt-Enter turns all your values into strings, rendering them difficult to work with. Your data from the example should be entered into three rows, at a minimum, allowing formulas access to the numeric values you are using.

**MrShorty** · 03-26-2015, 04:50 PM

This is why I don't like using alt-enter to enter multiple items in one cell. IMO, spreadsheets are easiest to program when a cell contains only one piece of information. My advice would be to separate each "line" in those cells into separate cells.

If you insist on having multiple values in a single cell, then you will need to become familiar with Excel's text manipulation functions: https://support.office.com/en-us/art...__toc309306717 (Left, Right, Mid will probably be most important) and learn how to use them to extract each individual value from the text string.

**SteveBIM** · 03-26-2015, 05:21 PM

Thanks guys.

I originally had the data in separate cells spread out in a longish line, which was a bit messy.

I was looking for a more elegant solution, but it doesn't look as though I can do it except if I put the data on separate rows for each of the three outcomes and repeat the basic game details.