+ Reply to Thread
Results 1 to 6 of 6

Sort Values in Bar Chart from High to Low

  1. #1
    Registered User
    Join Date
    02-28-2007
    Posts
    29

    Sort Values in Bar Chart from High to Low

    I have a bar chart that where I need to sort the values from high to low. Obviously, I could sort the source data, but these numbers change monthly and may not always rank in the same order. It's important that the source data stays in the same order once I set it.

    Is there some kind of function or way to name the range or something that will do this?

    Thanks!
    JP

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by jpgoeth
    I have a bar chart that where I need to sort the values from high to low. Obviously, I could sort the source data, but these numbers change monthly and may not always rank in the same order. It's important that the source data stays in the same order once I set it.

    Is there some kind of function or way to name the range or something that will do this?

    Thanks!
    JP
    Hi,


    Assuming that your data in currently labels in column A and amounts in column B, (say, A1:B10)

    Insert a new column A, and in A1 put

    =RANK(C1,C$1:C$10)+COUNTIF(C1:C$10,C1)-1

    in D1 out

    =VLOOKUP(ROW(),A$1:C$10,2,FALSE)

    in F1 put

    =VLOOKUP(ROW(),A$1:C$10,3,FALSE)

    and formula fill the three down to the extent of your data, then chart from D1:F10

    As your data in Column C changes so your chart should adjust.

    hth
    ---
    Last edited by Bryan Hessey; 05-18-2007 at 09:18 AM.
    Si fractum non sit, noli id reficere.

  3. #3
    Registered User
    Join Date
    02-28-2007
    Posts
    29

    Question

    Bryan,

    Thanks for your reply - I'm having trouble making it work though. I'm not really understanding how the formulas work.

    Could you explain the COUNTIF part? I'm not sure what its purpose is here.

    Thank you!
    JP

    Edit: Even though I don't understand the countif part, I think the more important thing is the VLOOKUP function (since that's where I'm getting the error!). I have a feeling that there is a problem with the row reference, but I don't know how to fix it.
    Last edited by jpgoeth; 05-18-2007 at 06:13 PM.

  4. #4
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by jpgoeth
    Bryan,

    Thanks for your reply - I'm having trouble making it work though. I'm not really understanding how the formulas work.

    Could you explain the COUNTIF part? I'm not sure what its purpose is here.

    Thank you!
    JP

    Edit: Even though I don't understand the countif part, I think the more important thing is the VLOOKUP function (since that's where I'm getting the error!). I have a feeling that there is a problem with the row reference, but I don't know how to fix it.
    The countif is to allow for 'equal' values and give them a unique standing.

    If you are having problems understanding the range then perhaps copy some test data to A1:B10 of a new sheet, and follow the instructions from there, perhaps that will demonstrate more clearly. Alternately you could post some sample of the data you are trying to chart.
    The 'row()' reference is a count of 1 to 10 in my example.
    If your data does not start on row 1, but rather starts on row 3, then you will need to use row()+2

    hth
    ---

  5. #5
    Registered User
    Join Date
    02-28-2007
    Posts
    29

    Thumbs up Aha!

    I figured it out - I just misunderstood what you were saying before.

    Thanks for your help!

  6. #6
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by jpgoeth
    I figured it out - I just misunderstood what you were saying before.

    Thanks for your help!
    good to see, and thanks for your response.

    ---

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1