What's new
Fantasy Football - Footballguys Forums

Welcome to Our Forums. Once you've registered and logged in, you're primed to talk football, among other topics, with the sharpest and most experienced fantasy players on the internet.

Excel Question (1 Viewer)

kutta

kutta

Footballguy
I am somewhat Excel literate, but by no means am I an expert. So, here's what I need to do.

I have a bunch of rows of data for projected projects in 2017. The last column in the rows is "2017 Revenue." The rows are sorted so that the biggest number in "2017 Revenue" is first, followed by the next biggest, etc. There are maybe 40 rows of data, with the last row being "Total 2017 Revenue" which sums the "2017 Revenue" column. Simple enough.

What I would like to do is color code the rows that make up 80% of the 2017 revenue. So let's say the Total 2017 Revenue is $20M, I would like to color code the rows, starting at the top (highest value project) until I get to a total amount of $16M ($20M * 0.80). So if the first six projects total 80% of the total, I would color code the first six rows.

Any thoughts on the best way to do this?

TIA

 
I'd poke around with conditional formatting.  You may need to use a '$'.

GL!

Ooh, even better:  check against the sum of that column divided by 2 (assuming it would always include the total row).  This way, you don't have to worry about the next report having more rows.

 
Last edited by a moderator:
In conditional formatting, you can pick "top 80%" of values, Just don't select the total cell if you don't want it highlighted. There's probably a cleaner way to do it with a formula, but for a quick and dirty method, that should do it. 

 
Nathan R. Jessep said:
In conditional formatting, you can pick "top 80%" of values, Just don't select the total cell if you don't want it highlighted. There's probably a cleaner way to do it with a formula, but for a quick and dirty method, that should do it. 
Click to expand...
I don't see this option in conditional formatting. That would work perfectly I think.

 
Walking Boot said:
Are you OK with using a 'helper column'? You'd put a row to the right of the end of your data, then hide it from sight. Let me get to a PC, I think I can crank it out pretty quick. 
Click to expand...
I would prefer not to, but it's not a huge deal to do that.

 
Psychopav said:
I'd poke around with conditional formatting.  You may need to use a '$'.

GL!

Ooh, even better:  check against the sum of that column divided by 2 (assuming it would always include the total row).  This way, you don't have to worry about the next report having more rows.
Click to expand...
I am poking around with conditonal formatting but I'm not able to get exactly what I want.

 
Nathan R. Jessep said:
In conditional formatting, you can pick "top 80%" of values, Just don't select the total cell if you don't want it highlighted. There's probably a cleaner way to do it with a formula, but for a quick and dirty method, that should do it. 
Click to expand...
OK. I found it, but it highlights everything for some reason. I'll play around with this - I think it's what I want.

 
Got it!

Thanks Nathan. I needed to select "Top 10%" and change it to Top 20%. Exactly what I needed.

I appreciate the help.

 
With a helper column, sorted in descending order:

If F2 is the first cell with a number, and G2 has the running total, set the Condl Formatting to =G2< [total] *.8 

Then fill down the formatting to the rest of the cells.

 
kutta said:
I am somewhat Excel literate, but by no means am I an expert. So, here's what I need to do.

I have a bunch of rows of data for projected projects in 2017. The last column in the rows is "2017 Revenue." The rows are sorted so that the biggest number in "2017 Revenue" is first, followed by the next biggest, etc. There are maybe 40 rows of data, with the last row being "Total 2017 Revenue" which sums the "2017 Revenue" column. Simple enough.

What I would like to do is color code the rows that make up 80% of the 2017 revenue. So let's say the Total 2017 Revenue is $20M, I would like to color code the rows, starting at the top (highest value project) until I get to a total amount of $16M ($20M * 0.80). So if the first six projects total 80% of the total, I would color code the first six rows.

Any thoughts on the best way to do this?

TIA
Click to expand...
If your first revenue is in A2 for example and sum is in A102,

Go to conditional formatting

Manage Rules

New Rule

Use a formula to determine which cells to format

=IF(SUM(A$2:A2)/$A$102>0.8,FALSE,TRUE)

Under format...chose the blue color in "fill"

Hit ok

Copy A2, paste formatting down to A101.

 
Last edited by a moderator:
Walking Boot said:
This is what I was getting at, keep a running total in Column G and hide that column. G2 =F2, then G3 is =G2+F3; G4 is =G3+F4, and so on.

Conditional format all cells that are >= the total times 0.8.

Only issue is this will miss the very last one that puts you "over the top".
Click to expand...
Ah, right.

 
tjnc09 said:
If your first revenue is in A2 for example and sum is in A102,

Go to conditional formatting

Manage Rules

New Rule

Use a formula to determine which cells to format

=IF(SUM(A$2:A2)/$A$102>0.2,FALSE,TRUE)

Under format...chose the blue color in "fill"

Hit ok

Copy A2, paste formatting down to A101.
Click to expand...
I like this, but it's not working for me. I see the A2 switching to A3, A4, etc. as I go down, so I'm pretty sure I have the formula correct, but I'm not getting any highlights.

 
Walking Boot said:
That's right, @tjnc09 has it correct, and without the helper column.

When you copy A2, make sure you hit "paste special" and select only the button for "Formats"
Click to expand...
Man, I'm starting to feel like a dope. I have the formula he posted, but I can't get it to copy down correctly. It copies to A3 correctly (the first copy), but every cell after that has A3 instead of A4, A5, etc.

Not sure what I'm doing wrong.

 
Last edited by a moderator:
kutta said:
Man, I'm starting to feel like a dope. I have the formula he posted, but I can't get it to copy down correctly. It copies to A3 correctly (the first copy), but every cell after that has A3 instead of A4, A5, etc.

Not sure what I'm doing wrong.
Click to expand...
Under conditional formatting->manage rules->you can set "Applies to" the range that have your individual revenues as well.  Try that and see if it helps instead of copying the formats down.  In my example, If my individual revenues are in A2 to A101, Applies to would show =$A$2:$A$101

 
OK. I got it, but I had to change the 0.2 in the original formula to 0.8, which makes sense to me, but I'm not sure why it works for Walking Boot.

 
Walking Boot said:
I still can't get it to work in some special cases, even with a helper row, though.

If the product hits exactly 80% on the nose, it's fine. If it falls just short, the product that puts it "over the top" isn't highlighted.

Doing it the other way, it will highlight the product that puts it "over the top", however, it highlights one too many rows if one product hits the 80% number exactly.
Click to expand...
And it's OK if it's not exact. I just need an estimate of our top projects for the year, so I can play with that number a bit.

 
Walking Boot said:
I still can't get it to work in some special cases, even with a helper row, though.

If the product hits exactly 80% on the nose, it's fine. If it falls just short, the product that puts it "over the top" isn't highlighted.

Doing it the other way, it will highlight the product that puts it "over the top", however, it highlights one too many rows if one product hits the 80% number exactly.
Click to expand...
I wasn't sure if he wanted over the top. 

=IF((SUM($A$2:$A2)-A3)/$A$102>0.8,FALSE,TRUE)

subtracting the next cell down will accomplish getting > 80%.

*also exclude the last cell in the range.

 
Last edited by a moderator:
kutta said:
OK. I got it, but I had to change the 0.2 in the original formula to 0.8, which makes sense to me, but I'm not sure why it works for Walking Boot.
Click to expand...
Yeah, I edited that.  I misread your original need for 80%.  I think WB saw the edit. 

 
Well, it works but I'm honestly not quite sure how it's working. It doesn't seem to be copying correctly, but it obvioulsy is because it works. If I take a random row (let's say 23), and look at what it says when I select "conditional formatting" for that cell, I get:

=IF(SUM(X$2:X2)/$X$42>0.81,FALSE,TRUE)

(note that my "X" is "A" in the original formula).

I would expect to see =IF(SUM(X$2:X23)/$X$42>0.81,FALSE,TRUE).

Is it just that you don't see the formula change in the cells for the formatting, but it actually does? If I copy the formula and just paste it in a cell next to the column, I see that number change the entire way down.

 
Alright, since that was such a resounding success, how about this one.

Right now I sort on Column X, highest to lowest. But I do it manually. The data runs from X2 to X41. I'd like for it to sort automatically whenever X changes.

I think I have to use a macro, but I've never done that before. Any suggestions on a relatively easy way to do this?

 
kutta said:
Well, it works but I'm honestly not quite sure how it's working. It doesn't seem to be copying correctly, but it obvioulsy is because it works. If I take a random row (let's say 23), and look at what it says when I select "conditional formatting" for that cell, I get:

=IF(SUM(X$2:X2)/$X$42>0.81,FALSE,TRUE)

(note that my "X" is "A" in the original formula).

I would expect to see =IF(SUM(X$2:X23)/$X$42>0.81,FALSE,TRUE).

Is it just that you don't see the formula change in the cells for the formatting, but it actually does? If I copy the formula and just paste it in a cell next to the column, I see that number change the entire way down.
Click to expand...
That is just how Excel displays the formulas in conditional formatting.  As long as the row numbers aren't fixed with a $, it will copy down in conditional formatting exactly how normal formulas copy down.

Is there a reason you want to auto-sort?  Clicking Data->Filter->hit the little triangle in A1 for "Sort Largest to Smallest" takes a second.

 
tjnc09 said:
That is just how Excel displays the formulas in conditional formatting.  As long as the row numbers aren't fixed with a $, it will copy down in conditional formatting exactly how normal formulas copy down.

Is there a reason you want to auto-sort?  Clicking Data->Filter->hit the little triangle in A1 for "Sort Largest to Smallest" takes a second.
Click to expand...
True. It's not really a big deal but I'm a fan of automating as much as possible because I'm lazy.

But you're right. It's not a really big deal.

 
kutta said:
Alright, since that was such a resounding success, how about this one.

Right now I sort on Column X, highest to lowest. But I do it manually. The data runs from X2 to X41. I'd like for it to sort automatically whenever X changes.

I think I have to use a macro, but I've never done that before. Any suggestions on a relatively easy way to do this?
Click to expand...
You can have macros run when a sheet changes and then later code for specific ranges.  The below code will autosort your worksheet using Col X in descending order whenever a cell in X2:X41 is changed.

  1. Right click your tab and choose 'View Code'.  This will take you to VBA.
  2. Change the left dropdown to 'Worksheet' and then change the right dropdown to 'Change'.
  3. Here's the code that will sort cells A2:X41, assuming you have headers.  You'll need to tweak the stuff in red to fit whatever you need.
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Range("X2:X41"), Target) Is Nothing Then
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("X1"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A2:X41")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End If

End Sub

 
Ned said:
You can have macros run when a sheet changes and then later code for specific ranges.  The below code will autosort your worksheet using Col X in descending order whenever a cell in X2:X41 is changed.

  1. Right click your tab and choose 'View Code'.  This will take you to VBA.
  2. Change the left dropdown to 'Worksheet' and then change the right dropdown to 'Change'.
  3. Here's the code that will sort cells A2:X41, assuming you have headers.  You'll need to tweak the stuff in red to fit whatever you need.
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Range("X2:X41"), Target) Is Nothing Then
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("X1"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A2:X41")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End If

End Sub
Click to expand...
OK. This works. But, it only works if I manually type a number in column X. Right now, column X is a calculated value (=SUM(L13:W13)). When I change the "changable" cell(s) that Colum X refers to, column X doesn't reorder.

 
How do you do this with text? Say I want to highlight a whole row, based on text in one cell.

Cells have this: RB1, WR3, TE5....... If I want to highlight all rows with RB in one cell for instance, how do I do that?

 
Mr.Pack said:
How do you do this with text? Say I want to highlight a whole row, based on text in one cell.

Cells have this: RB1, WR3, TE5....... If I want to highlight all rows with RB in one cell for instance, how do I do that?
Click to expand...
In conditional formatting:

=IF(LEFT($A1,2)="RB",TRUE,FALSE) as one rule

=IF(LEFT($A1,2)="WR",TRUE,FALSE) as another

=IF(LEFT($A1,2)="TE",TRUE,FALSE) as last rule, etc.

if column A has your RB1, WR3, TE5.  Set "Applies to" to cover all rows and columns you want highlighted

 
Last edited by a moderator:
kutta said:
OK. This works. But, it only works if I manually type a number in column X. Right now, column X is a calculated value (=SUM(L13:W13)). When I change the "changable" cell(s) that Colum X refers to, column X doesn't reorder.
Click to expand...
OK, I should've asked you about that.  See blue changes...needed to change the A1:X41 to cover the headers.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Range("L2:W41"), Target) Is Nothing Then
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("X1"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:X41")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End If

End Sub

 
Ned said:
OK, I should've asked you about that.  See blue changes...needed to change the A1:X41 to cover the headers.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Range("L2:W41"), Target) Is Nothing Then
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("X1"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:X41")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End If

End Sub
Click to expand...
Awesome! That works. Thanks so much for your help. I appreciate it.

With my auto-color-coding and auto-sorting, I'm going to look really smart  :nerd:

 
kutta said:
Awesome! That works. Thanks so much for your help. I appreciate it.

With my auto-color-coding and auto-sorting, I'm going to look really smart  :nerd:
Click to expand...
Well, kind of...

I just realized you can't undo with CTRL-Z when using a macro. Hmmm. I'm not so sure this is going to work now. Pretty tough to not be able to undo mistakes.

 
kutta said:
Well, kind of...

I just realized you can't undo with CTRL-Z when using a macro. Hmmm. I'm not so sure this is going to work now. Pretty tough to not be able to undo mistakes.
Click to expand...
Not sure what you're workbook looks like, but you can have the macro copy the entire sheet to a backup sheet prior to the sorting.  That gives you a snapshot in time that you can copy/paste back if you needed it.

 
tjnc09 said:
In conditional formatting:

=IF(LEFT($A1,2)="RB",TRUE,FALSE) as one rule

=IF(LEFT($A1,2)="WR",TRUE,FALSE) as another

=IF(LEFT($A1,2)="TE",TRUE,FALSE) as last rule, etc.

if column A has your RB1, WR3, TE5.  Set "Applies to" to cover all rows and columns you want highlighted
Click to expand...
Thank you, I'll give it a try!

 
Ned said:
Not sure what you're workbook looks like, but you can have the macro copy the entire sheet to a backup sheet prior to the sorting.  That gives you a snapshot in time that you can copy/paste back if you needed it.
Click to expand...
That sounds cool, but I think I'll just stick to manually sorting. Even though I really liked the auto-sort...

 
You must log in or register to reply here.

Similar threads

Senor Schmutzig
Chicago Winter Sports Trifecta Coming Up! - Help wanted
Replies
2
Views
275
Senor Schmutzig
Senor Schmutzig
The General
*** Official PLUMBING EMERGENCY!!1! Thread*** BIG UPDATE AT POST 25
2
Replies
65
Views
1K
Rustoleum
Rustoleum
parasaurolophus
Home warranty question
Replies
10
Views
483
parasaurolophus
parasaurolophus

Users who are viewing this thread

Total: 2 (members: 0, guests: 2)
Top