| 0 |
Subject: Excel Formatting Question
Posted by: Boxman
- [571114225] Wed, Jun 25, 2008, 15:09
I want to set up a conditional formatting in Excel for the five highest numbers in one column where those numbers are highlighted yellow.
I know I can sort the numbers to get this data but that would screw up my spreadsheet format. |
| 1 | Razor
ID: 545172413
Wed, Jun 25, 2008, 15:49
|
The quick and dirty way would be to pull your data into another table, sort it, determine the value for the 5th highest value and then do conditional formatting such that >= that value is highlighted in yellow.
|
| 2 | J
Leader
ID: 049346417
Wed, Jun 25, 2008, 15:50
|
The only thing I can think of is to figure out what the 5th highest number is, then set the conditional formatting to "if A1 > XX"
Not sure how big your SS is though
You could copy, then paste special/values into a new spreadsheet and sort there to figure it out
|
| 3 | youngroman
ID: 3751268
Wed, Jun 25, 2008, 15:53
|
for the top 5 in column B select B2 to B100 and do a conditional formatting with this formula:
=RANK(B2;B$2:B$100)<6
|
| 4 | Boxman
ID: 571114225
Wed, Jun 25, 2008, 16:57
|
youngroman: Awesome. That worked. Thank you.
|
| 6 | C1-NRB
ID: 2911103011
Mon, Mar 30, 2009, 12:29
|
Little help, please. I'm working in Office Suite 2007.
I want two columns of numbers to be expressed as a ratio in a third column.
Example: I need a formula that will show the result "4:1" in column "C" when column "A" has a value of 16 and column "B" has a value of 4. I've tried searching "ratio" in Excel Help but don't get what I want.
|
| 7 | Guru
ID: 330592710
Mon, Mar 30, 2009, 12:40
|
... and what should appear if the numbers are 3 and 2? Or 9 and 6?
|
| 8 | Frick
ID: 3410551012
Mon, Mar 30, 2009, 12:56
|
I was going to suggest using the Concatenate function, but you might need an if statement.
A simple Concatenate would look like:
=CONCATENATE(A1,":",B1)
I think you could also try using the GCD function.
Evaluate the two numbers with GCD =GCD(A1,B1). Add two columns and Divide A1 and B1 with the result of the GCD column. Then use the Concatenate formula on the two new columns.
So your statement should now look like
|
| 9 | C1-NRB
ID: 2911103011
Mon, Mar 30, 2009, 13:09
|
... and what should appear if the numbers are 3 and 2? Or 9 and 6?
I was thinking of using the simplest possible formula; something along the lines of (using your numbers) 3/2:2/2 which would come out as 1.5:1
For multi-decimal results, round to tenths (0.1)
My first two amounts are 3363 and 8. I want Excel to not just "do the math" (420.375) but show the result "420.4:1"
|
| 10 | weykool
ID: 2842717
Mon, Mar 30, 2009, 13:31
|
The concatenate that frick mentioned should do the trick.
Divide A by B and place in C.
D would be:
=CONCATENATE(c1,":1")
|
| 11 | Guru
ID: 330592710
Mon, Mar 30, 2009, 14:00
|
This will accomplish the rounding:
=TEXT(A1/B1,"0.0")&":1"
|
| 12 | C1-NRB
ID: 2911103011
Mon, Mar 30, 2009, 15:08
|
Great. Thanks guys. I knew ya'll would come through.
Concatenate worked but Guru's =text took out the additional column step.
|
|
|
|
Post a reply to this message: (But first, how about checking out this sponsor?)
|