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?

---
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,784
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

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

SprintStory PointsCumulative
133
158
14.512.5
1315.5
15.521
1324
2327
2330
2333
2841

<colgroup><col><col><col></colgroup><tbody>
</tbody>


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

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,784
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! :eek: 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!
 

Forum statistics

Threads
1,077,664
Messages
5,335,568
Members
399,025
Latest member
alce

Some videos you may like

This Week's Hot Topics

Top