Sum values based on pattern in array

M

mibrahim

New Member
Joined
Dec 1, 2011
Messages
23
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:

[TABLE="class: cms_table, width: 192"]
<tbody>[TR]
[TD="class: cms_table_xl66, width: 64"]91[/TD]
[TD="class: cms_table_xl66, width: 64"]91[/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, width: 64"]92[/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, width: 64"]93[/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, width: 64"]94[/TD]
[TD="class: cms_table_xl66, width: 64"]94[/TD]
[TD="class: cms_table_xl66, width: 64"]3[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, width: 64"]95[/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, width: 64"]96[/TD]
[TD="class: cms_table_xl66, width: 64"]96[/TD]
[TD="class: cms_table_xl66, width: 64"]2[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, width: 64"]97[/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, width: 64"]98[/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, width: 64"]99[/TD]
[TD="class: cms_table_xl66, width: 64"]99[/TD]
[TD="class: cms_table_xl66, width: 64"]3[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, width: 64"]100[/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, width: 64"]99[/TD]
[TD="class: cms_table_xl66, width: 64"]99[/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, width: 64"]98[/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, width: 64"]97[/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, width: 64"]96[/TD]
[TD="class: cms_table_xl66, width: 64"]96[/TD]
[TD="class: cms_table_xl66, width: 64"]3[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, width: 64"]95[/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, width: 64"]96[/TD]
[TD="class: cms_table_xl66, width: 64"]96[/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, width: 64"]97[/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, width: 64"]96[/TD]
[TD="class: cms_table_xl66, width: 64"]96[/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, width: 64"]95[/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, width: 64"]94[/TD]
[TD="class: cms_table_xl66, width: 64"]94[/TD]
[TD="class: cms_table_xl66, width: 64"]-2[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, width: 64"][/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, width: 64"][/TD]
[TD="class: cms_table_xl65, width: 64"]Net Total[/TD]
[TD="class: cms_table_xl66, width: 64"]9[/TD]
[/TR]
</tbody>[/TABLE]


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 :smile:

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

Thanks!
 
Sort by date Sort by votes
Hi

Assuming I have understood your question correctly, I have got it to work as follows using some helper columns:
[TABLE="width: 517"]
<TBODY>[TR]
[TD]Data</SPAN>
[/TD]
[TD]Indicator</SPAN>
[/TD]
[TD]Change</SPAN>
[/TD]
[TD]Last Change</SPAN>
[/TD]
[TD]Count</SPAN>
[/TD]
[/TR]
[TR]
[TD]91</SPAN>
[/TD]
[TD]91</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]92</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]2</SPAN>
[/TD]
[TD]1</SPAN>
[/TD]
[/TR]
[TR]
[TD]93</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]2</SPAN>
[/TD]
[TD]1</SPAN>
[/TD]
[/TR]
[TR]
[TD]94</SPAN>
[/TD]
[TD]94</SPAN>
[/TD]
[TD]3</SPAN>
[/TD]
[TD]2</SPAN>
[/TD]
[TD]1</SPAN>
[/TD]
[/TR]
[TR]
[TD]95</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]5</SPAN>
[/TD]
[TD]1</SPAN>
[/TD]
[/TR]
[TR]
[TD]96</SPAN>
[/TD]
[TD]96</SPAN>
[/TD]
[TD]2</SPAN>
[/TD]
[TD]5</SPAN>
[/TD]
[TD]1</SPAN>
[/TD]
[/TR]
[TR]
[TD]97</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]7</SPAN>
[/TD]
[TD]1</SPAN>
[/TD]
[/TR]
[TR]
[TD]98</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]7</SPAN>
[/TD]
[TD]1</SPAN>
[/TD]
[/TR]
[TR]
[TD]99</SPAN>
[/TD]
[TD]99</SPAN>
[/TD]
[TD]3</SPAN>
[/TD]
[TD]7</SPAN>
[/TD]
[TD]1</SPAN>
[/TD]
[/TR]
[TR]
[TD]100</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]10</SPAN>
[/TD]
[TD]1</SPAN>
[/TD]
[/TR]
[TR]
[TD]99</SPAN>
[/TD]
[TD]99</SPAN>
[/TD]
[TD][/TD]
[TD]10</SPAN>
[/TD]
[TD]2</SPAN>
[/TD]
[/TR]
[TR]
[TD]98</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]10</SPAN>
[/TD]
[TD]2</SPAN>
[/TD]
[/TR]
[TR]
[TD]97</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]10</SPAN>
[/TD]
[TD]2</SPAN>
[/TD]
[/TR]
[TR]
[TD]96</SPAN>
[/TD]
[TD]96</SPAN>
[/TD]
[TD]3</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]2</SPAN>
[/TD]
[/TR]
[TR]
[TD]95</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]15</SPAN>
[/TD]
[TD]1</SPAN>
[/TD]
[/TR]
[TR]
[TD]96</SPAN>
[/TD]
[TD]96</SPAN>
[/TD]
[TD][/TD]
[TD]15</SPAN>
[/TD]
[TD]2</SPAN>
[/TD]
[/TR]
[TR]
[TD]97</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]15</SPAN>
[/TD]
[TD]2</SPAN>
[/TD]
[/TR]
[TR]
[TD]96</SPAN>
[/TD]
[TD]96</SPAN>
[/TD]
[TD][/TD]
[TD]15</SPAN>
[/TD]
[TD]3</SPAN>
[/TD]
[/TR]
[TR]
[TD]95</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]15</SPAN>
[/TD]
[TD]3</SPAN>
[/TD]
[/TR]
[TR]
[TD]94</SPAN>
[/TD]
[TD]94</SPAN>
[/TD]
[TD]-2</SPAN>
[/TD]
[TD]15</SPAN>
[/TD]
[TD]3</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

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
 
Last edited:
Upvote 0
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).

[TABLE="width: 336"]
<colgroup><col span="3"><col><col></colgroup><tbody>[TR]
[TD]Data[/TD]
[TD]Indicator[/TD]
[TD]Change[/TD]
[TD]Last Change[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD="align: right"]91[/TD]
[TD="align: right"]91[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]92[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]93[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]94[/TD]
[TD="align: right"]94[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]95[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]96[/TD]
[TD="align: right"]96[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]97[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]98[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]99[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]99[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]98[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]97[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]96[/TD]
[TD="align: right"]96[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]95[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]96[/TD]
[TD="align: right"]96[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]97[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]96[/TD]
[TD="align: right"]96[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]95[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]94[/TD]
[TD="align: right"]94[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Net Total[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks!
 
Upvote 0
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
 
Upvote 0
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:

[TABLE="width: 336"]
<colgroup><col span="3"><col><col></colgroup><tbody>[TR]
[TD]Data[/TD]
[TD]Indicator[/TD]
[TD]Change[/TD]
[TD]Last Change[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD="align: right"]91[/TD]
[TD="align: right"]91[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]92[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]93[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]94[/TD]
[TD="align: right"]94[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]95[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]96[/TD]
[TD="align: right"]96[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]97[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]98[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]99[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]99[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]98[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]97[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]96[/TD]
[TD="align: right"]96[/TD]
[TD][/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]95[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]96[/TD]
[TD="align: right"]96[/TD]
[TD][/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]97[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]96[/TD]
[TD="align: right"]96[/TD]
[TD][/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]95[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]94[/TD]
[TD="align: right"]94[/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Net Total[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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
 
Upvote 0
FYI - this is what it looks like when I enter the formula in cell C3 as you suggested earlier:

[TABLE="width: 336"]
<colgroup><col span="3"><col><col></colgroup><tbody>[TR]
[TD]Data[/TD]
[TD]Indicator[/TD]
[TD]Change[/TD]
[TD]Last Change[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD="align: right"]91[/TD]
[TD="align: right"]91[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]92[/TD]
[TD][/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]93[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD="align: right"]94[/TD]
[TD="align: right"]94[/TD]
[TD][/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD="align: right"]95[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD="align: right"]96[/TD]
[TD="align: right"]96[/TD]
[TD][/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD="align: right"]97[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD="align: right"]98[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD="align: right"]99[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD="align: right"]99[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD="align: right"]98[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD="align: right"]97[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD="align: right"]96[/TD]
[TD="align: right"]96[/TD]
[TD][/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD="align: right"]95[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD="align: right"]96[/TD]
[TD="align: right"]96[/TD]
[TD][/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD="align: right"]97[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD="align: right"]96[/TD]
[TD="align: right"]96[/TD]
[TD][/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD="align: right"]95[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD="align: right"]94[/TD]
[TD="align: right"]94[/TD]
[TD][/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Net Total[/TD]
[TD="align: center"]#VALUE![/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks
 
Upvote 0
Per my earlier post I got this to work exactly as I described. Can you post the formulas you are using?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
This is what I'm seeing as a result of stepping into the formula in cell C3

Evaluation: =1*ABS(#VALUE!)
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Formula for the day total. i want to multiply the qty sold by price for each item and need the sum of the items sold in a day
Replies
2
Views
213
acarwreck
A
T
  • Solved
Help on putting a number into a range
2 3
Replies
22
Views
340
Peter_SSs
Peter_SSs
A
  • Solved
Need to transform columns-lists into a coincidence matrix (array)
Replies
9
Views
284
Cubist
Cubist
Sandeep Warrier
  • Question Question
Calculate No. of Working Days based on Shift Pattern
Replies
2
Views
336
Tetra201
Tetra201
D
  • Solved
Calculation with changes in time. Update.
Replies
4
Views
323
Davebro
D

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top