Expert Graph needed

LebD

New Member
Joined
Sep 30, 2021
Messages
33
Office Version
  1. 365
Platform
  1. Windows
I want to create the following graph, and I am not succeeding at it yet, so help is needed from an expert charter:

I have a complicated set of data with different indicators but 2 variables I am interested in. So I will create a graph for each.
I will simplify it below:

first column is Names. Second column is Start date (format 01.01.2021). Third Column is End Date. Fourth column is 1st variable.
I want to chart a period axis spreading let's say over 2 years. and the Y axis representing the variable of the 4th column.

The start date is important because it is represented on the X axis.

When a Name has a start date, it should trigger a vertical bar on the graph above the X axis corresponding to the amount in 4th column.
When a name has an End Date, it should trigger a vertical bar on the graph below the X axis on the negative side corresponding to the amount in 4th column too.

chart I think will get messy with all the data overlapping each other. So it should show Top 10 for each tick of the X axis over all the date period I selected like from 1.1.2021 till 31.12.2022

Advice appreciated
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

ravi2628

Board Regular
Joined
Dec 20, 2017
Messages
209
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Names will be based on your selection or need to be plotted automatically.



I have Designed a Sample Graph I think this can help you sharing with sample snapshots.


Book5
ABCDEFGH
1NamesStart DateEnd dateVariable
2Ravi01.01.2021 10Ravi
3Suresh 03.01.202140
422.11.2020
501.01.202110
610.02.2021
Sheet1
Cell Formulas
RangeFormula
H5H5=IF(VLOOKUP(G2,A1:B3,2,0)=" ",VLOOKUP(G2,A1:D3,4,0)*-1,VLOOKUP(G2,A1:D3,4,0))
G4G4=G5-40
G5G5=IF(VLOOKUP(G2,A1:D3,2,0)=" ",VLOOKUP(G2,A1:D3,3,0),VLOOKUP(G2,A1:D3,2,0))
G6G6=G5+40
Cells with Data Validation
CellAllowCriteria
G2ListRavi,Suresh



1633946477992.png



1633946395184.png
 

LebD

New Member
Joined
Sep 30, 2021
Messages
33
Office Version
  1. 365
Platform
  1. Windows
Thank you but names are plotted automatically. From the same list you selected as source data.
So I decided to go down the pivot table road. but have troubles selecting Top 10 per month.
 

Attachments

  • page1.png
    page1.png
    62.1 KB · Views: 7
  • sheet1.png
    sheet1.png
    119.3 KB · Views: 7

LebD

New Member
Joined
Sep 30, 2021
Messages
33
Office Version
  1. 365
Platform
  1. Windows
I changed the Stacked 100% chart to Stacked (value) and added a filter to show values greater than x. So getting closer to the target
 

Forum statistics

Threads
1,147,747
Messages
5,742,960
Members
423,767
Latest member
dafydd_jwc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top