Start New Sum When Certain Criteria Is Met

Hi,

"I want to calculate N9:N10 not N2 as I have it just now.

I cannot see any formula which currently involves a sum over the range N2:N10. The closest you have is a formula containg SUM($N$2:N9) in cell N10.

Could you clarify?

Regards

So can you give your desired results for these three cells (N8:N10)?

What should the calculation be in N8?
What should the calculation be in N9?
What should the calculation be in N10?

Regards

Ok, but the way I see to get a result of £1.31 in cell N10 is by changing the formula to:

=IF(C9<>1,(SUM(N9)/B10)+F10,F10)

I thought you were suggesting that it should be:

=IF(C9<>1,(SUM(N8:N9)/B10)+F10,F10) (which in this case would give £1.52).

Could you re-phrase your statements of what N8, N9 and N10 should be in terms of which range of cells should be used in the summation for each of them?

Regards

Ok, I think I'm nearly there, but just to be clear, are you saying that, hypothetically, if we had a situation in which, say cell C100 was <>1, and the last time that it was =1 was cell C50, then the formula in N100 should be:

=IF(C100<>1,(SUM(N50:N99)/B100)+F100,F100)

Or should this be with N51:N99?

Is this correct? Why do you only divide by the last Odds_Numeric, out of interest, and not take the sum of all previous Stakes divided by their respective Odds_Numeric, i.e. sum of (N51/B51), (N52/B52), (N53/B53), ..., (N100/B100)?

Sorry for not getting this more quickly - nearly there, I think!

Regards

You wouldn't need to recover a stake that wasn't lost so if C50 was =1 then N50 stake was fine. The sum would start from N51. I only divide by the most recent Odds_Numeric as that is the odds on offer for the forthcoming event. All other Odds_Numeric data is redundant as soon as the result is in.

So if the sum of 10 losers in a row is £10 and the next bet is priced at 5/1 (displayed as 5 in the column as it doesn't include the stake) your recovery would be 10/5 = £2 for the recovery plus your current stake as shown in the corresponding column F.

Sorry, I don't mean to be repetitive, but to do the Excel work I could really do with you confirming that, in my above scenario, the formula in N100 would be:

=IF(C100<>1,(SUM(N51:N99)/B100)+F100,F100)

Unfortunately the betting logic behind your explanations is a little lost on me, so if we can stay in 'Excel' language that would be great (for me!).

Regards

Actually, looking at your logic, in my example shouldn't the formula in N100 be rather:

IF(C99<>1,(SUM(N51:N99)/B100)+F100,F100)?

Regards

Hi,

This is still a bit experimental, but can you try this array formula (confirm with CTRL+SHIFT+ENTER, not just ENTER) in N3 (leave your current formula in N2) and copy down:

=IF(C2<>1,SUM(N2:INDEX($N$1:N2,IFERROR(MATCH(10^10,IF($C$1:C2=1,ROW($C$1:C2)))+1,2)))/B3+F2,F3)

Let me know what sort of results you get and whether they match your expectations and we can work from there.

Regards

You're welcome. And don't worry about the formula! I probably made it more complex than it needs to be, but still, it works...

Best of luck with it, and if you get any issues further down the line, just give me a shout here.

Cheers

If you're interested in learning how it works, you can go through it step by step using the Evaluate Formula feature (in the Formulas tab).

Takes a bit of getting used to, but it's an excellent way to learn how formulas work.

Other than that, I'd be happy to explain it here if you want.

Regards

Ok, so taking an example at random, the formula in N13 would be:

=IF(C12<>1,SUM(N12:INDEX($N$1:N12,IFERROR(MATCH(10^10,IF($C$1:C12=1,ROW($C$1:C12)))+1,2)))/B13+F12,F13)

Assuming the first part is TRUE (C12<>1), and that, for example, the last occurrence of a 1 in the range C1:C12 is in C7, then, for the sake of simplicity, the only thing we need to be concerned with is making sure that the range being passed to the SUM formula is N8:N12.

The first thing to point out is that a nice, often-overlooked feature of Excel is that it will recognise ranges from right-to-left as well as from left-to-right, and from down-to-up as well as from up-to-down: type e.g. =SUM(A3:A1) in a cell and Excel will calculate the sum of the three values in A1, A2 and A3, just as if you had entered =SUM(A1:A3) (in fact, it will actually 'correct' your formula for you in this case). Similarly =SUM(C1:A1) gives the same result as =SUM(A1:C1).

So, with that in mind, we need to make sure that the part in red of this part of the formula:

SUM(N12:INDEX($N$1:N12,IFERROR(MATCH(10^10,IF($C$1:C12=1,ROW($C$1:C12)))+1,2)))

returns the cell address N8, since we will then have SUM(N12:N8), which is SUM(N8:N12) as required.

So, we are looking to find the row number of the last "1" in the range C1:C12 (=C7), add 1 to this row number (=C8), and then this will give us our corresponding entry in the N column (=N8).

This part of the formula:

IF($C$1:C12=1,ROW($C$1:C12)

checks whether each of the entries in C1:C12 is equal to 1 or not (effectively performs 12 separate IF statements), and returns an array consisting of the row number of that cell (if TRUE) and FALSE (if FALSE). In this example, the array would be:

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;7;FALSE;FALSE;FALSE;FALSE;FALSE}

This array is then passed into the MATCH formula, which has now become:

MATCH(10^10,{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;7;FALSE;FALSE;FALSE;FALSE;FALSE})

The reason for the choice of 10^10 is simply that it is an extremely large number and, knowing that, if it does not find the lookup value anywhere within the array, the MATCH formula (with the correct parameter) will return the last number in the array less than or equal to the lookup value, we will guarantee that we will get just that. (Technically, it's not the largest number available in Excel, and so potentially this formula could give incorrect results, e.g. if it were applied to an array containing 10^10+1, but in practical terms it's usually sufficient.)

Hence we get the value 7 and, seeing that we then add 1 to this result, our formula has now become:

INDEX($N$1:N12,IFERROR(8,2))

The reason for the IFERROR part, which tells the formula to return 2 if that is the case, is to account for cases where there are no "1"s in the previous rows of column C, in which case our previous MATCH function, not being able to find any value - never mind 10^10 - amongst all the FALSEs, would return an error. We would then have:

INDEX($N$1:N12,IFERROR(#N/A,2)) = INDEX($N$1:N12,2)= N2 and so, correctly, we would be summing all the way back to the starting row.

Clearly, however, in our case '8' is not an error and so our formula becomes:

INDEX($N$1:N12,8) which evaluates to N8. QED.

Regards