Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 2 of 2

Thread: Rank Values By Bold Font

  1. #1
    Join Date
    28th April 2012
    Posts
    1

    Rank Values By Bold Font

    Hi all!

    In the below example i need to only rank the topline (bold records) and ignore all the other values from the value column when ranking. The excel rank formula ranks "office" as 3 because "detached" (which is a row to be ignored when ranking) has a higher value. It should however be ranked 2 as i have manually illustrated below...Note the bold rows are a total of the regular font rows directly below it...

    Any ideas on a formula that can help me rank only the values in bold while ignoring all other values from the column?

    Example:

    Rank Description Value
    1 House 5,000,000
    Detached 3,000,000
    Semi 1,000,000
    Terrace 1,000,000
    2 Office 2,000,000
    1st Floor 1,000,000
    2nd Floor 1,000,000

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,483

    Re: Rank select cells only from a column

    UDF, if you like

    1) Hit Alt + F11 to open VBE
    2) go to [Insert] - [Module] then paste the code onto the right pane.
    3) hit Alt + F11 again to get back to excel

    Use like

    =IFERROR(RankBold(C2,$B$2:$C$8,0),"")

    VB:
    Function RankBold(myvalue, rng As Range, order) As Long 
        Dim x As Range, r As Range, n As Long 
        Application.Volatile 
        For Each r In rng.Columns(1).Cells 
            If r.Font.Bold Then 
                If x Is Nothing Then 
                    Set x = r(, 2) 
                Else 
                    Set x = Union(x, r(, 2)) 
                End If 
            End If 
        Next 
        RankBold = WorksheetFunction.Rank(myvalue, x, order) 
    End Function 
    
    
    RankBold.xlsm

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Rows Font Style Bold
    By javed2011 in forum EXCEL HELP
    Replies: 5
    Last Post: March 19th, 2012, 03:15
  2. Replies: 2
    Last Post: July 29th, 2011, 09:47
  3. Bold font in visual basic editor
    By Darrenpowell in forum EXCEL HELP
    Replies: 3
    Last Post: March 30th, 2011, 04:57
  4. Printer Prints Non-Bold Font In Bold
    By gdrew in forum EXCEL HELP
    Replies: 3
    Last Post: May 6th, 2008, 10:58
  5. change font in bold for subtotals only in Pivottable
    By Paul20091968 in forum EXCEL HELP
    Replies: 6
    Last Post: July 3rd, 2006, 15:57

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno