Sum Rows, Draw Line one certain value met

mrhaboobi

New Member
Joined
Jan 31, 2019
Messages
2
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?

---
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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.
 
Upvote 0
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
 
Upvote 0
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! :eeek: 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!
 
Upvote 0
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 callTotal 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​
 
Upvote 0
Welcome to the Board!

Yes, that's possible. Consider:

Book1
ABC
1Hours on callTotal hours on callTotal hours on call (version 2)
2131313
313.526.526.5
42450.550.5
564114.5114.5
6112226.5226.5
7200426.5426.5
851477.5477.5
921498.5498.5
1021500519.5
111029.529.5
122554.554.5
131771.571.5
14200271.5271.5
15199470.5470.5
1656500526.5
1777.5104104
18104104
Sheet10
Cell Formulas
RangeFormula
B2:B18B2=IF(B1=500,MOD(SUM($A$1:$A1),500)+A2,MIN(500,SUM(B1,A2)))
C2:C18C2=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!
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,157
Members
453,021
Latest member
Justyna P

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