Win Loss Record

[Snarkdark]

I'm currently trying to create a formula that automatically calculates a win/loss record based on increases and decreases in postgame rating. The following formula was working great for me until we decided to make changes to how we record time spent.

 =CONCATENATE(SUMPRODUCT(--(F14:F44<F15:F45))&"W-"&(SUMPRODUCT(--(F14:F44>F15:F45))-1&"L")) <f15:f45))&"w-"&(sumproduct(--(f14:f44><f15:f45))&"w-"&(sumproduct(--(f14:f44><f15:f45))&"w-"&(sumproduct(--(f14:f44>

The goal is an output of something like 3W-2L, and the formula worked well.

We decided it was also important to keep track of when breaks were taken between matches, so we started formatting the records as follows:

[TABLE="width: 200"]
<tbody>[TR]
[TD]3338[/TD]
[/TR]
[TR]
[TD]3363[/TD]
[/TR]
[TR]
[TD]BREAK[/TD]
[/TR]
[TR]
[TD]3386[/TD]
[/TR]
[TR]
[TD]3419[/TD]
[/TR]
[TR]
[TD]3387[/TD]
[/TR]
[TR]
[TD]3362[/TD]
[/TR]
[TR]
[TD]BREAK[/TD]
[/TR]
[TR]
[TD]3389[/TD]
[/TR]
</tbody>[/TABLE]

Comments are added to the breaks to denote why it was taken and/or what was done during the time, all in the name of analysis and improvement.

The issue we face now is that these BREAKs break the formula as the it tries to find if a number is larger or smaller than the BREAK cell, leading to inaccurate record counts.

I'm relatively uneducated and very rusty in Excel so the answer may be a very simple one, but can anyone think of a solution to this issue? I know moving the breaks to a different section would make it an easy fix, but seeing the when and why of the pauses alongside the rises and falls of the ratings is very helpful in our overall analysis.</f15:f45))&"w-"&(sumproduct(--(f14:f44></f15:f45))&"w-"&(sumproduct(--(f14:f44></f15:f45))&"w-"&(sumproduct(--(f14:f44>

 

[Peter_SSs]

Welcome to the MrExcel board!

Could we use a helper column?

Excel Workbook
	F	G	H
14	3338
15	3363	W
16	BREAK
17	3386	W
18	3419	W
19	3387	L
20	3362	L
21	BREAK
22	3389	W	4W-2L
23
W-L

 

[Snarkdark]

That would for sure be a easy solution, but I was hoping to keep it to one column.

 

[Snarkdark]

My reasoning for this is that each column meant to be a record of a single session, with the surrounding columns being records of the last and the next session. Conditional formatting with colors ranging from red->green for low to high is applied to each column to easily show trends in wins and losses with comments on certain cells to note circumstances for particularly low points and high points, so I'd like to keep the data as condensed as possible.

 

[Peter_SSs]

That would for sure be a easy solution, but I was hoping to keep it to one column.

So where would the result for F14:F45 go?

If it is, say, just below that range (F47 in my example below) couldn't the helper cells go below that (eg in F49 down?). Rows 49, 50, 51 ... could be hidden if required.

Excel Workbook
	F
14	3338
15	3363
16	BREAK
17	3386
18	3419
19	3387
20	3362
21	BREAK
22	3389
23
43
44
45
46
47	4W-2L
48
49	W
50
51	W
52	W
53	L
54	L
55
56	W
77
W-L

If this is still not feasible/acceptable, perhaps a user-defined function using vba would be?