CODEWITHSUNDEEP
excelworksheet-functionarray-formulas
prolink007
prolink007

Reputation: 34554

Adding variable amount of values, based on numeric values

Problem Solved

Cheesy Mistake... lol

For my time column when i was using my milliseconds i was using the column in my sheet that i was not mentioning. I was using my millisecond interval column. I thought this would work, but forgot that my new formula was based on the specific time, not the interval of time since this last thing was seen. So the sum was always less than 60000. Thanks for all your help and wish i could accept two answers. =)

Revisiting the whole question

Below are some of my actual values. I was using seconds earlier, but in my real document i am using milliseconds. I need the sumoflist column to be the sum of minorsums that are 60 seconds old from the current time. This would be 60000 milliseconds.

Here is the EXACT formula that i was using from earlier. And yes, i was entering it correctly and it was still not working. I had to modify it a little so that it would work with my sheet and milliseconds.

{=SUM(IF($B2-$B$2:$B2<60000,$U$2:$U2))}
{=COUNT(IF($B2-$B$2:$B2<60000,$U$2:$U2))}

The summing function will sum all of them, no matter how old they are. The same for the counting.

Since i gave you small chunk of the data, use 20000 for examples. Thanks to all those that have helped me so far! And please continue helping me! <3

// csv for easy import
seconds,sumoflist,minorsums
800,0,0
1000,40000,40000
1200,80000,40000
1000,120000,40000
800,160000,40000
1000,200000,40000
800,240000,40000
1000,280000,40000
1200,320000,40000
1000,360000,40000
800,400000,40000
1000,440000,40000
800,480000,40000
1000,520000,40000
1200,560000,40000
1000,600000,40000
800,640000,40000
1000,680000,40000
800,720000,40000
1000,760000,40000
1200,800000,40000
1000,840000,40000
800,880000,40000
1000,920000,40000
800,960000,40000
1000,1000000,40000
1200,1040000,40000
1000,1080000,40000
800,1120000,40000
1000,1160000,40000
800,1200000,40000
1000,1240000,40000
800,1280000,40000
850,1282500,2500
900,1285000,2500
850,1287500,2500

Upvotes: 1

Views: 193

Answers (2)

Dante May Code
Dante May Code

Reputation: 11247

For the first cell (next to 0), type in

=SUM(IF((A:A<=A2)*(A:A>A2-60),C:C,0))

and press CTRL + SHIFT + ENTER to confirm(, and there will be {} around the formula after you have done so).

Then drag down.

enter image description here

Here you see, the answers provided by Excellll and me are both correct.

For 61.6, 1.8 - 61.6 are summed, while for 63.25, 4 - 63.25 are summed.

To make sure, the { and } are around the formula, which appear after CTRL + ALT + ENTER.

That is, press CTRL and hold, press ALT and hold, press ENTER and release 3 simultaneously.

Upvotes: 2

Excellll
Excellll

Reputation: 5785

Dante Jiang's suggestion is right, but the full column references slow down the calculation considerably. Try this instead:

=SUM(IF($A2-$A$2:$A2<60,$C$2:$C2))

Press Ctrl+Shift+Enter to enter the formula as an array formula. Then fill down.

Upvotes: 2

Related Questions