Avatar of Bright01
Bright01
Flag for United States of America asked on

Excel Graphic - Designed to readjust to Model

I have a great little Spreadsheet that keeps getting better and better thanks to the people at EE.  I'm looking for a Line-chart graphic that depects any of 5 items that are in a dynamic range.  In the Spreadsheet, you select the Model Horizon (i.e. # of years) and it automatically establishes and populates the table (beginning in column E) !  What I'm looking for is a nice graphical representation of the data (Rows 5 - 9) in the table that will "self adjust" depending on the changes to the cells.  Also check the "reset" to see how it works once the model is cleared.  Check it out.............

Much thanks in advance.

B.
Client-LTV-Assessment-v6.xlsm
Microsoft Excel

Avatar of undefined
Last Comment
Bright01

8/22/2022 - Mon
Ingeborg Hawighorst (Microsoft MVP / EE MVE)

Hello Bright,

you can create dynamic range names for the X axis categories and the data series in rows 5 to 9, along these lines

chtXLabels      =OFFSET(LTV!$D$4,0,0,1,COUNTA(LTV!$4:$4)-3)
chtActiveClients      =OFFSET(chtXLabels,1,0)
chtCOGS      =OFFSET(chtXLabels,2,0)
etc. for all the other rows

Use these formulas in the data source for the series definitions and the Category (X) Labels.

See attached.

cheers, teylyn
Copy-of-Client-LTV-Assessment-v6.xlsm
Ingeborg Hawighorst (Microsoft MVP / EE MVE)

I've only applied the formula for the first two data series ...
Bright01

ASKER
Teylyn,

Great to hear from you!  Hope all is well.  

Thank you for the quick reply and I see now how you make it dynamic using the range names.  In now seeing how it turned out, I was wondering if you could take one more pass at this.  I don't know how to set up a y2 axis (in a bar chart) on the same chart, but what I really need to plot given the differences in the numbers is the Rev. on the y1 axis (which is done -- just need to remove COGS), and Net Profit (Row 9) on a bar graph using the y axis.  I know this is different then the original requirements/request, but after seeing it, I now know what can best represent the changes made across time.  If you can do that, I'll take it from there.

Much thanks!

B.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Ingeborg Hawighorst (Microsoft MVP / EE MVE)

Hang on, help me understand this:

you want a combination line/column chart, right?

The line series should be sourced from row ___________
The column series should be on the secondary axis and be sourced from ____________

Please fill in the blanks.

cheers, teylyn

Bright01

ASKER

LIne Chart y1, Row = 5
Column Chart y2, Row = 9


TY
ASKER CERTIFIED SOLUTION
Ingeborg Hawighorst (Microsoft MVP / EE MVE)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Bright01

ASKER
Great next step.  Much thanks.  As professional as it gets.

B.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.