# Thread: Excluding zeros in Charts (2003)

1. ## 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. ## 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. ## Re: Excluding zeros in Charts (2003)

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

Regards

4. ## Re: Excluding zeros in Charts (2003)

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

Steve

5. ## 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. ## 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
•