Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Excluding zeros in Charts (2003)

    Hi Loungers,

    I followed a thread for excluding zero values in chart and got the attached example. I can see what happens, but I was hoping that someone could explain how the different formulas work and how to refer to the names in the x & y axis in the chart or any other tips you may have.

    Thanks for you help

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excluding zeros in Charts (2003)

    Steve is not online at the moment, so I'll fill in for him.

    There are three important parts to the working of this worksheet:

    1. The formulas in H1:L12 are used to create a copy of A1:B12 sorted on column B in descending order, then on column A in ascending order.

    The formulas in column H calculate the rank of the corresponding row in A1:B12 according to this sort order: the number of cells with a greater value in column B, plus the number of cells with the same value in column B, but a lesser value (alphabetically) in column A. The result is increased by 1 to make the rank begin at 1.

    Column I contains the fixed values 1 to 12, sorted ascending.

    Column J uses MATCH to calculate where the corresponding number in I occurs in column H, i.e. the row containing the data with the specified rank.

    Column K uses INDEX to look up the item with that rank in column A, and column L does the same for column B.

    The result is that K1:L12 is a sorted copy of A1:B12.

    2. Dynamic named ranges have been defined to determine the values in K1:L12 with a non-zero value in column L.

    See Insert | Name | Define

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Excluding zeros in Charts (2003)

    Hans - Thanks very much for the explanation - That help alot!!

    Regards

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excluding zeros in Charts (2003)

    Hans,
    Thank you very much for answering the question while I was offline.

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excluding zeros in Charts (2003)

    Hans,

    I finally got some time to work on this using my own data. I've got step 1 & 2 working fine, however I can't get the y axis to recognise yValues name or the x axis to recognise the xCat name. I activated the Series tab as you described and typed in YValues and xCat into the appropriate locations but the chart does not seem to recognise it and I get an invalid reference error. I'm obviously doing something wrong - any ideas?

  6. #6
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excluding zeros in Charts (2003)

    Hans - I found the problem - It was me!!

Posting Permissions

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