Sum Rows, Draw Line one certain value met

[mrhaboobi]

Hi Team

Im trying to do something fairly simple but failing

Lets say i have 10 rows

each row has a value

1
2
10
20
24
56

and so on.

In one column i have a Cumulative count

1
3
13
33

and so on.

Based on the cumulative count i want to be able to draw a line under a row

If ( cumulativeValue > 10) then draw line under row. ( or Draw line under the previous row. )

thats the first simple formatting thing i would like to do.

Id also like it so that the cumulative count can reset itset itself so that the count start from the row which had a line under it

eg

1
3
13
-------
20

In short im trying to create a sheet that can Chunk data up ( rows ) based on the sum of a value in one of the cell. Everytime i reach a threshold i want to draw a line, and reset the count back.

Any thoughts?

---

 

[Eric W]

Welcome to the Board.

Let's assume you have headers in row 1, and your values are in column A. Put this formula in B2:

=IF(SUM(B1)>=10,A2,SUM(B1,A2))

Drag down the column as needed. Now select columns A:B, click Conditional Formatting > New Rule > Use a formula > and enter:

=$B1>=10

Click Format... > Border > click the solid line from Style, and the Underline from the Border box, then click OK.

 

[mrhaboobi]

Welcome to the Board.

Let's assume you have headers in row 1, and your values are in column A. Put this formula in B2:

=IF(SUM(B1)>=10,A2,SUM(B1,A2))

Drag down the column as needed. Now select columns A:B, click Conditional Formatting > New Rule > Use a formula > and enter:

=$B1>=10

Click Format... > Border > click the solid line from Style, and the Underline from the Border box, then click OK.

Super hero

Does exactly what i want, ive changed the forumla to point to a cell to deteermine the threshold value. The question i have new to improve it one little bit more, is the first column where it says Sprint.

[TABLE="width: 302"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Sprint[/TD]
[TD]Story Points[/TD]
[TD]Cumulative[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]4.5[/TD]
[TD="align: right"]12.5[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]15.5[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]5.5[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]27[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]41[/TD]
[/TR]
</tbody>[/TABLE]


essentially id like that value to be set to 1 or 2 or 3, but all the row within the group would have the same value.

eg in the example above with a Threshold of 20 the line would be ruled under the row with value 21, and the first column would all say 1. the next block would be 2 and so on per block. Is there way to increment those numbers the same way as you do along with the cumulative value?

Lastly whats a good resource to learn these "more advanced" technique?

Thanks

 

[Eric W]

The "Sprint" formula should just be a variation of the original formula, something like:

=IF(OR(SUM(B1)>=10,ROW()=2),SUM(C1,1),C1)

Assuming you want this in column C, put this in C2 and copy down.


As far as finding Excel resources, there is a vast wealth of options, it just depends on your time, needs, budget, and style of learning. Books, videos, sample problems, trainers, and more. A longtime member here has compiled an incredible list of web resources which you can find here:

https://www.mrexcel.com/forum/general-excel-discussion-other-questions/1084627-best-vba-book.html

Scroll down until you see the list. The list is a bit daunting! But don't get overwhelmed, nobody knows everything about Excel. Just look for something that looks interesting, or useful for something you're working on, work on it until you get it, then try something else. If you want a more structured approach, you can get a book (the Dummy's books are usually pretty good), or there are a series of YouTube videos you can follow. There are links to those in the list too.

Good luck!

 

[bluesfanmtv]

Welcome to the Board.

Let's assume you have headers in row 1, and your values are in column A. Put this formula in B2:

=IF(SUM(B1)>=10,A2,SUM(B1,A2))

Drag down the column as needed. Now select columns A:B, click Conditional Formatting > New Rule > Use a formula > and enter:

=$B1>=10

Click Format... > Border > click the solid line from Style, and the Underline from the Border box, then click OK.

THANK YOU FOR THIS!!! I have found this formula to be a springboard to what I am looking to achieve. Rather than an underline, I have set the conditional format to highlight the cell after reaching 500, now my question:
I am tracking hours on call and I need a reset at 500, but I also need the residual number to carry over into the next 500 hours total...so let's say my "Hours" total 519.5, can I carryover the remaining 19.5 in my next 500 count?

Hours on call	Total hours on call
13​	13​
13.5​	26.5​
24​	50.5​
64​	114.5​
112​	226.5​
200​	426.5​
51​	477.5​
21​	498.5​
21​	519.5​
	0​
	0​

 

[Eric W]

Welcome to the Board!

Yes, that's possible. Consider:

Book1
	A	B	C
1	Hours on call	Total hours on call	Total hours on call (version 2)
2	13	13	13
3	13.5	26.5	26.5
4	24	50.5	50.5
5	64	114.5	114.5
6	112	226.5	226.5
7	200	426.5	426.5
8	51	477.5	477.5
9	21	498.5	498.5
10	21	500	519.5
11	10	29.5	29.5
12	25	54.5	54.5
13	17	71.5	71.5
14	200	271.5	271.5
15	199	470.5	470.5
16	56	500	526.5
17	77.5	104	104
18		104	104
Sheet10

Cell Formulas
Range	Formula
B2:B18	B2	=IF(B1=500,MOD(SUM($A$1:$A1),500)+A2,MIN(500,SUM(B1,A2)))
C2:C18	C2	=MOD(N(C1),500)+A2

The B formula will cap the total at 500, then continue with the overage on the next line. The C formula will have the value exceeding 500, but continues on the next line with the overage. So essentially, the rows will all be the same except where the totals exceeds 500. Your preference on how to treat that. The C formula is a lot simpler.

Hope this helps!

 

[bluesfanmtv]

LIFE SAVER!!! Thank you so much for that!!
-Mike

 

[Eric W]

Happy to help!