• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Plotting Two non numeric values for the same numeric data

Nishesh

Nishesh

New Member
Hi from Wellington,

I have a single numeric data (Cheapest value) for which I want to plot two non-numeric values. In attached example, I have around 40 products (Products) for which I have identified the cheapest value and also identified which organization (cheapest vendor) will provide me that product.
Whats the best way to chart it as I could not do it by adding secondary axis. Also I could not download any other add'ins as suggested on the site due to restrictions with the place that I work with. I also could not edit each label with the formula with Custom labels (as suggested on the Chandoo site) as there are too many data points.

Please guide.
 

Attachments

  • Plotting non numeric value for 40 data items.xlsx
    9 KB · Views: 6
@Nishesh

Hello from Wellington. It looks like a stunning day to waste it on Excel. But since we both have it open, here we go.

The simplest way to solve this is by just selecting your data and inserting a regular column chart. Excel sees two columns of labels and automatically shows both under the column with the column height encoding the value. If you now sort your list by vendor, it might provide interesting insights.
You could use pivot charts so the labels are nicely grouped by vendor.
 
Thank you r2c2.

Cannot agree more on the Wellington weather, we don't get this often.

I get your point over the basic chart, problem is I have 40 products and thus it gets a bit clingy. Also, I was looking at some way of Conditional Formatting as I have 5 colors for 5 different vendors.
Is there a work-around you could think of which makes the viewing more user friendly.

Thanks
 
Hi ,

With 40 products , no chart is going to look viewer friendly.

I suggest you just display the results in the form of a matrix , with the products as row labels and the vendors as column headers ; within this matrix use one colour to highlight the cheapest vendor for each product.

Narayan
 
@Nishesh

I second Narayan's opinion. With so many products, you are going to get clutter no matter what. I suggest one of the below options:

Pivot chart option:
  • Set up your data as a pivot with product in row labels, vendor in column labels and price as value. Then create a pivot chart (bar or column)
  • Now, click on the bars, press CTRL+1 and set overlap to 100% and gap with to less than 50%. As for each product there is only one vendor, you will get colored bars. Since you have only 5 vendors, the colors may not be too confusing (or they may be..). Sort your products so they are lined by vendor. This might be useful.
Data bars option:
  • This is my preferred choice. Just keep your data as is and add data bars to the value column. Sort your table by vendor. Color / band rows so you can spot vendors and see which products go where. Done.
 
You must log in or register to reply here.
Back
Top