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

The formula in cell D3 needs to reference column C, not column B. Make sure you copy the corrected formula down.

The image you tried to post appears to be corrupt - I'm seeing lots and lots of characters and no image, so you may want to try that again.

If you step through each part of the formula (by clicking 'Evaluate' repeatedly) can you see which part of the formula isn't working?

Andrew
 
Upvote 0
It looks like we cross-posted.

Delete the space in cell B3. This should fix the #VALUE error.

I had assumed that blank cells had nothing in them. Is deleting the space an option?

Andrew
 
Last edited:
Upvote 0
Ya, sorry about that. Firefox always give me trouble with screen shots. I posted the evaluated error just before you sent this last reply.

In my evaluation, I'm seeing the error in this part of the formula from cell C3: =1*ABS(#VALUE!)

The formula in D3 is referencing column C but that's why I'm getting errors everywhere.

The error is in the last portion of the formula in C3: "ABS(B3-INDEX(B$1:B3,D3))"

the formulae in D3 and E3 are working properly. It's just C3 that's giving me trouble.

Thanks
 
Upvote 0
Cross-posted again:

I failed to mention that column B is a formula. Is there a way to reference the result of this formula without getting this error?

thanks
 
Upvote 0
Assuming B3 contains a single space (and there is always a single space in place of a value), then change the formula in cell C3 to this:

=IF(B3<>" ",IF(OR(E3<3,ISEVEN(E3)),1,-1)*ABS(B3-INDEX(B$1:B3,D3)),0)

If you don't want to see the zero values, then format the cell so that the zero values do not show.

Andrew
 
Upvote 0
Thanks so much Andrew!

I got it to work with =IF(B3<>"",IF(OR(E3<3,ISEVEN(E3)),1,-1)*ABS(B3-INDEX(B$1:B3,D3)),0)

Now, I have to rethink my logic since it doesn't seem to produce exactly what I was looking for. Your formulae were perfect! I just need to tweak some stuff now. I may come back here for some tweaking tomorrow.

Cheers
m
 
Upvote 0
Thanks for the feedback and I'm pleased we got there in the end.

I'd be curious to see your formula you are using for column B........or not (depending on how complex it is!).

Cheers
Andrew
 
Upvote 0
Ha,

Be careful what you wish for: I'm trying to populate column B only if the value of column A crosses 1, 4, 6, 9, 11, 14, 16, 19, 21, 24, 26, 29, 31, etc... (see the pattern). So, if A2 is .5 and A3 is 1.5, then I want B3 to be 1.5. Also, if A4 is 15 and A5 is 13.9, then I want B5 to be 13.9. But, if A6 is 17 and A7 is 18, then I don't want B7 to populate with anything... until the next value in column A crosses either 19 or 16. Basically, I only want column B to populate with the value in column A if column A crosses a number that ends in 1, 4, 6, or 9.

I haven't written the full formula yet. I was trying to test the first part of my logic - then realized this may be a more daunting task than I bargained for.


Feel free to take a shot - if you dare :)
 
Upvote 0
Interesting question. I thought you might have had this part solved. I have a formula that gives the correct answer most of the time but it fails on a sequence where the number turns on itself like 95 > 94 > 95. I believe the formula needed for a decreasing number is different to the formula needed for an increasing number, but this is corrupted when the number turns on itself.

There are two main components to this, being:
=INT((A3-1)/5)<>IF(A3>=A2,INT((A2-1)/5),INT((A4-1)/5))
but it returns the incorrect result for the sequence 97 > 96 > 95 > 96 > 97.

and this part:
=INT((A3-4)/5)<>IF(A3>=A2,INT((A2-4)/5),INT((A4-4)/5))
Which hasn't yet returned any incorrect values - although I haven't fully tested it.

Wrapped inside an OR function these two formulae pick up most values, but they occasionally have errors (like the one above and the sequence 108.5 > 106 > 103.5 > 101.5). They miss the 103.5 value and incorrectly report the 101.5 value.

So - an interesting challenge but I'm going to need to give this some more thought.

There is an option for a formula like this:
=(MAX(IF(MOD(INT(A57),10)={1,4,6,9},1,0))*(INT(A57)<>INT(A56)))=1
but it only works where the values change by 1 or less.

I will need to get back to you.
Andrew
 
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