Sum values based on pattern in array

[mibrahim]

Hello People,

I have one (middle) column of numbers and I want to have a running total as a certain pattern appears as shown in the numbers below:

91	91
92
93
94	94	3
95
96	96	2
97
98
99	99	3
100
99	99
98
97
96	96	3
95
96	96
97
96	96
95
94	94	-2
	Net Total	9

<tbody>
</tbody>

You'll notice that 3 is the difference between 91 and 94. 2 is the difference between 96 and 94. 3 is the difference between 99 and 96. 99 repeats twice, so we continue to add 3, which is the difference between 96 and 99. Then, 96 is repeated 3x so we now subtract 2, which is the difference between 94 and 96. If the numbers would continue to decrease one or two at a time, the formula should continue to add the differences until the number is repeated 3 or more odd times. As long as it's repeated once or an even amount of times, it should continue to add the difference instead of subtract it. It should only subtract the difference if the number is repeated 3 or more odd times. I want to be able to copy this down 30k rows again - but don't worry about how many columns it takes to come to a conclusion. My comp can handle it

Hope I was clear. Lemme know if you need any more clarification.

Thanks!

 

[Andrew Fergus]

Hi

Assuming I have understood your question correctly, I have got it to work as follows using some helper columns:

Data</SPAN>	Indicator</SPAN>	Change</SPAN>	Last Change</SPAN>	Count</SPAN>
91</SPAN>	91</SPAN>
92</SPAN>			2</SPAN>	1</SPAN>
93</SPAN>			2</SPAN>	1</SPAN>
94</SPAN>	94</SPAN>	3</SPAN>	2</SPAN>	1</SPAN>
95</SPAN>			5</SPAN>	1</SPAN>
96</SPAN>	96</SPAN>	2</SPAN>	5</SPAN>	1</SPAN>
97</SPAN>			7</SPAN>	1</SPAN>
98</SPAN>			7</SPAN>	1</SPAN>
99</SPAN>	99</SPAN>	3</SPAN>	7</SPAN>	1</SPAN>
100</SPAN>			10</SPAN>	1</SPAN>
99</SPAN>	99</SPAN>		10</SPAN>	2</SPAN>
98</SPAN>			10</SPAN>	2</SPAN>
97</SPAN>			10</SPAN>	2</SPAN>
96</SPAN>	96</SPAN>	3</SPAN>	10</SPAN>	2</SPAN>
95</SPAN>			15</SPAN>	1</SPAN>
96</SPAN>	96</SPAN>		15</SPAN>	2</SPAN>
97</SPAN>			15</SPAN>	2</SPAN>
96</SPAN>	96</SPAN>		15</SPAN>	3</SPAN>
95</SPAN>			15</SPAN>	3</SPAN>
94</SPAN>	94</SPAN>	-2</SPAN>	15</SPAN>	3</SPAN>

<TBODY>
</TBODY>

Assuming your data starts in row 2, then enter the following array formula into cell D3:
{=MAX(IF(C$2:C2<>0,ROW(C$2:C2),2))}
Don't enter the curly brackets, but after you have entered the formula (but before you press the Enter key) instead press the Ctrl & Shift & Enter keys at the same time. You will know you have done this correctly when the curly brackets {} automatically appear around the formula. This converts the formula to an array formula. This part is trying to find the last row in which the value changed in column C. If you data starts on another row let me know because we will need to amend the formulas (or start the formulas off in row 3 then insert new rows above as required).

In cell E3 enter the following formula:
=COUNTIF(INDEX(B$1:B2,D3):B3,INDEX(B$1:B2,D3))

This provides the count that we later test.

Then in cell C3 enter the following formula:
=IF(OR(E3<3,ISEVEN(E3)),1,-1)*(B3>0)*ABS(B3-INDEX(B$1:B3,D3))
Which provides the answer you want.

Copy all 3 formulas down. These formulas assume you have already populated column B with the values you want.

I'm not sure how this will handle thousands of rows of data. There may be a more efficient way of doing this.

I trust this helps.
Andrew

 

[mibrahim]

Andrew,

Thanks very much for the help! So far, my 'Last Change' and 'Count' columns are coming out fine (matching what you have shown above), but the 'Change' (column C) is giving me a #VALUE! error. My data has headers in row 1 and the data begins with '91' in row 2.

To clarify: assume I have no data at all in column C (labeled 'Change'). I only inserted the numbers 3, 2, 3, 3, and -2 for display purposes so that you may know what my desired result is. Currently, you can assume I have this: (and I want column C to populate the numbers I just mentioned (3, 2, 3, 3, and -2).

Data	Indicator	Change	Last Change	Count
91	91
92			2	1
93			2	1
94	94		2	1
95			2	1
96	96		2	1
97			2	1
98			2	1
99	99		2	1
100			2	1
99	99		2	1
98			2	1
97			2	1
96	96		2	1
95			2	1
96	96		2	1
97			2	1
96	96		2	1
95			2	1
94	94		2	1
	Net Total	0

<colgroup><col span="3"><col><col></colgroup><tbody>
</tbody>

Thanks!

 

[Andrew Fergus]

Hi
It looks like the formula in column D has not been correctly entered - it has not been confirmed as an array formula. Select the cell D2, press the F2 key to get into edit mode, then press and hold the Ctrl key (and while holding the Ctrl key down) press the Shift Key (and while holding both keys down) and then press the Enter key. You should see curly brackets {} automatically appear around the formula. Copy the formula in cell D2 down. I believe this is the error given the value hasn't changed from 2 all the way down - whereas per the example I posted the value in column D changed.
Andrew

 

[mibrahim]

Hi Andrew,

The formula in cell D3 has been entered as an array (with brackets) but it is referencing column C (which has no values at the moment). When I enter the formula you suggested for column C, then I get a "#VALUE!" error. If I alter the formula in cell D3 to reference column B instead of C, the result is as follows:

Data	Indicator	Change	Last Change	Count
91	91
92			2	1
93			3	2
94	94		4	1
95			5	1
96	96		6	1
97			7	1
98			8	2
99	99		9	1
100			10	1
99	99		11	1
98			12	1
97			13	2
96	96		14	1
95			15	1
96	96		16	1
97			17	1
96	96		18	1
95			19	1
94	94		20	1
	Net Total	0

<colgroup><col span="3"><col><col></colgroup><tbody>
</tbody>

To reiterate: I don't have any data for column C yet. I'm trying to solve for column C.

Logic for column C: find absolute value of difference from previous number if number is repeated 1, 2, 4, 6, 8, 10 times etc to infinity. OR, find negative value of difference if number is repeated 3, 5, 7, 9, 11 times etc to infinity. The end result should be as shown in the very first thread post.

Thanks!
m

 

[mibrahim]

FYI - this is what it looks like when I enter the formula in cell C3 as you suggested earlier:

Data	Indicator	Change	Last Change	Count
91	91
92		#VALUE!	2	1
93			#VALUE!	#VALUE!
94	94		#VALUE!	#VALUE!
95			#VALUE!	#VALUE!
96	96		#VALUE!	#VALUE!
97			#VALUE!	#VALUE!
98			#VALUE!	#VALUE!
99	99		#VALUE!	#VALUE!
100			#VALUE!	#VALUE!
99	99		#VALUE!	#VALUE!
98			#VALUE!	#VALUE!
97			#VALUE!	#VALUE!
96	96		#VALUE!	#VALUE!
95			#VALUE!	#VALUE!
96	96		#VALUE!	#VALUE!
97			#VALUE!	#VALUE!
96	96		#VALUE!	#VALUE!
95			#VALUE!	#VALUE!
94	94		#VALUE!	#VALUE!
	Net Total	#VALUE!

<colgroup><col span="3"><col><col></colgroup><tbody>
</tbody>

Thanks

 

[Andrew Fergus]

Per my earlier post I got this to work exactly as I described. Can you post the formulas you are using?

 

[Andrew Fergus]

Another recommendation : if C3 is still returning the #VALUE error, select that cell and step through the formula (look for the 'Evaluate Formula' menu option. In Excel 2010 see Formulas > Evaluate Formula, on older versions of Excel {e.g. 2003 onwards} it is under the 'Auditing Tools' menu option) and see which part of the formula is breaking. This will help resolve where the issue is coming from.
Andrew

 

[mibrahim]

Gladly,

I have data in columns A and B and I used the following formulas for C, D, and E:

C3: =IF(OR(E3<3,ISEVEN(E3)),1,-1)*(B3>0)*ABS(B3-INDEX(B$1:B3,D3))
D3: {=MAX(IF(B$2:B2<>0,ROW(B$2:B2),2))} (entered "=MAX(IF(B$2:B2<>0,ROW(B$2:B2),2))" then held Ctrl+Shift+Enter to apply brackets)
E3: =COUNTIF(INDEX(B$1:B2,D3):B3,INDEX(B$1:B2,D3))

When entering the aforementioned, I receive a "#VALUE!" error.

 

[mibrahim]

This is what I'm seeing as a result of stepping into the formula in cell C3

Evaluation: =1*ABS(#VALUE!)