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

3 data series but only one labor category?

D

DashboardNovice

Member
In the attached file, I am graphing the data in O21:Q22. The graph is fine, except everything is recognized as being in one x axis category. Under the blue column, I want the X category to say Principle, under Interest the category should say interest, and under monthly payment, the x axis category should say monthly payment.

I tried editing the horizontal axis labels and this did not work. Any suggestions?
 

Attachments

Have a look at teh attached
I used Data Labels to add the 3 Legends
Capture.PNG

I also adjusted the chart labels for the chart below that as per your previous post
Have a look at the formulas in the Name Manager which defines the vertical position of the labels
The two extra lines are on the secondary axis as a stacked line chart
so you need to keep the secondary axis scale the same as the primary axis
Capture2.PNG
 

Attachments

For the above Monthly Payment Chart
Consider changing the Y Primary Axis, Number Format to [<=1]0.00%;[>1]""
That way you won't see the 120%
Capture.PNG
 
Last edited:
Hui said:
Have a look at teh attached
I used Data Labels to add the 3 Legends
View attachment 7331

I also adjusted the chart labels for the chart below that as per your previous post
Have a look at the formulas in the Name Manager which defines the vertical position of the labels
The two extra lines are on the secondary axis as a stacked line chart
so you need to keep the secondary axis scale the same as the primary axis
View attachment 7332
Click to expand...

Wow! That's a lot. I was able to recreate what you did with the data labels. That's pretty cool.

Regarding the second part of your post, it looks like you are using virtual ranges which I just learned about yesterday from one of Narayan's replies to one of my posts. I'm trying to make sense of the formulas that you defined in Name Manager.

The range referring to interest is defined as: =(Sheet2!$U$23:$U$34)*0+0.2
The range referring to principle is defined as: =(Sheet2!$V$23:$V$34)*0+0.9

1. What is the purpose of multiplying each range by 0? What does that do?
2. Although you defined these ranges in Name Manager, what did you do to get them to actually plot?

3. Additionally, when I downloaded your file, both rows of data labels do not show values. Instead every single one looks like "[CELLRANGE]" without the quotes. Do you know why? It's as if a formula is not being evaluated properly.
 
Last edited:
Q1. =(Sheet2!$U$23:$U$34)*0+0.2
The =(Sheet2!$U$23:$U$34) part sets up an array of the values from U23:U34
Multiplying them by 0 converts the array to an array of 0's eg: ={0,0,0,0,0,0,0,0,0,0,0,0}
Adding a value of 0.2 or 1.0 changes the array to ={0.2,0.2,0.2,0.2,0.2,0.2,0.2,0.2,0.2,0.2,0.2,0.2}
which is the vertical location for the Labels

Q2 I used Excel 2013 which has the ability to link Data Labels to a Range
 
You must log in or register to reply here.
Back
Top