+ Reply to Thread
Results 1 to 16 of 16

How can I make a diagram like this?

  1. #1
    Registered User
    Join Date
    03-28-2021
    Location
    Italy
    MS-Off Ver
    365
    Posts
    9

    How can I make a diagram like this?

    Hi, I liked this diagram a lot which I have found on a article.

    I was wondering if it is possible to create such a chart in excel. Or maybe other programs were used for this purpose?
    Perhaps anybody can say my if this diagram was created by excel. Thanks

    Unbenannt.JPG

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: How can I make a diagram like this?

    Who knows where it was created....

    but... it's pretty much just a semi-logarithmic graph.

    see file
    Attached Files Attached Files
    Glenn



  3. #3
    Registered User
    Join Date
    03-28-2021
    Location
    Italy
    MS-Off Ver
    365
    Posts
    9

    Re: How can I make a diagram like this?

    Yeah it look quiet nice. But how can i calculate the data between year 1956 and 2020( For example for Harley Davidson)? If I know the value 1000 Dollars at beginning of the year 1956 and at the end date I had a value of 114000 dollar. Do I have to calculate the internal rate of return for this first?

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,289

    Re: How can I make a diagram like this?

    You have not told us what financial/mathematical model you intend to use for this. Your picture shows curves that have different start and end points and are concave down on a semi-log plot. The steps I would expect to go through to generate data for this:

    1) Choose your financial/mathematical model. I think a simple present value - future value model (so PV(), FV(), and related functions in Excel see help file for PV() function: https://support.microsoft.com/en-us/...1-da16e8168cbd ) can be used if you have no other idea what financial model to use.
    2) Come up with suitable values for the input parameters for the financial model. If you choose a simple pv model, this is mostly choosing appropriate values for rate and payment.
    3) Use your financial model to calculate the future value of $1000 at your different choices for the input parameters and at multiple years (arranged as Glenn's example shows where you have year in one column and future value in a second column).
    4) Insert an XY scatter chart and format/edit different elements until you get the look you want.

    It appears to me that you are currently "stuck" on steps 1 and 2 (choosing a financial model and/or coming up with input arguments for that model). I'm not sure how much we can help with those steps without more information from you. Help us understand exactly what you need to do here, and we should be able to help you program that into a spreadsheet.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Registered User
    Join Date
    03-28-2021
    Location
    Italy
    MS-Off Ver
    365
    Posts
    9

    Re: How can I make a diagram like this?

    Unfortunately, I don't know what calculation method is beeing used to make such a graph.

    Probably it should be a simple pv model. Probably I need to somehow simulate logarithmized returns from the start and end times themselves.

    1.JPG
    Last edited by damian89; 03-28-2021 at 12:48 PM.

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,289

    Re: How can I make a diagram like this?

    I'm not sure what to recommend, then. The short and useless answer is, yes, you can create a chart like that in Excel.

    If the chart's author did not specify the financial/mathematical model they used, does it matter what financial/mathematical model you choose? A simple present/future value model (using Excel's built in FV() function) seems like the easiest model to use. Enter a compounding interest rate in E1, an annual contribution/payment in E2, a starting year in E3, your initial (present) value in E4, then enter your future value function in B1 =FV($E$1,A1-$E$3,$E$2,$E$4) and copy down to the bottom of the list. Then adjust E1 and E2 until you get the curve you want. Repeat for the other auto companies and add those data to the chart. If you don't know the financial model and it doesn't really matter what you use, then this should work.

  7. #7
    Registered User
    Join Date
    03-28-2021
    Location
    Italy
    MS-Off Ver
    365
    Posts
    9

    Re: How can I make a diagram like this?

    Ok thanks I have implemented this.

    test.JPG

    But the diagram from Glenn Kennedy, looked more like what I want to have. Unfortunately I can't upload the file here directly.

    Can I edit the data to get a better result? So I mean because at the end I have a straight line. But I want to have an arc like in the photo of Glenn:
    2.JPG
    Last edited by damian89; 03-29-2021 at 06:39 AM.

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,289

    Re: How can I make a diagram like this?

    I'm still confident it is possible, but it is still not clear to me how you would like to calculate these values. The chart title in your picture in the OP talks about investment growth, and the FV() function (a basic present/future value compound interest model) was only the first guess at a possible model. If, after trying different values for rate and pmt, you decide that a simple present/future value model doesn't have the right shape, then simply pick a model that will have the right shape. My knowledge of financial models is rather limited, so I cannot recommend anything, but once we choose a suitable financial model, then it should be a matter of entering the financial model and a suitable set of input parameters for that model.

    If you really don't care at all what kind of model you use -- only that it can have a shape like Glenn's data -- I observe that I can approximate Glenn's data using a log-normal distribution. Something like =LOGNORMDIST(year-startyear+1,5.5,1)*1E6 is a reasonable approximation of Glenn's hand entered data. I have no idea what a log-normal distribution means as a financial model.

  9. #9
    Registered User
    Join Date
    03-28-2021
    Location
    Italy
    MS-Off Ver
    365
    Posts
    9

    Re: How can I make a diagram like this?

    Ok thank you

  10. #10
    Registered User
    Join Date
    03-28-2021
    Location
    Italy
    MS-Off Ver
    365
    Posts
    9

    Re: How can I make a diagram like this?

    Hi, it's me again. It won't let me go and I tried to plot the curve with a logarithmic trend line. I like the diagram very much. I just select the start and end date and the corresponding value. Then I enter the log trendline and delete the even.

    Now I have the problem that I have to create the data series manually. And I don't feel like doing this 350 times (my data size). How can I display this all at once?
    1.png


    I can't upload the file here so I uploaded this one:
    ww.file.io/download/REiy7irE2v0X

  11. #11
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,289

    Re: How can I make a diagram like this?

    I get a "file already deleted" error from that link. Is the new file too large to upload directly to the forum, or is there something else preventing you from uploading the file? If it's too large, we shouldn't need all of the data for all 350 data series in order to see what you are currently trying to do and come up with a different approach that won't be so tedious.

  12. #12
    Registered User
    Join Date
    03-28-2021
    Location
    Italy
    MS-Off Ver
    365
    Posts
    9

    Re: How can I make a diagram like this?

    When I would upload the file I can't select this button.

    1.png

  13. #13
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,289

    Re: How can I make a diagram like this?

    That paper clip icon thing has not worked for years (don't ask why, because none of us knows). There's a yellow banner at the top of the page describing the working procedure for attaching files.

  14. #14
    Registered User
    Join Date
    03-28-2021
    Location
    Italy
    MS-Off Ver
    365
    Posts
    9

    Re: How can I make a diagram like this?

    Ah ok. I think now it should work to upload the file. I have to select each dataset, like my picture before. Maybe this could be done even faster.
    Attached Files Attached Files

  15. #15
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,289

    Re: How can I make a diagram like this?

    The way you have arranged the data in the spreadsheet is awkward to work with when creating charts. It can work, but, as you've already discovered, it's tedious to work with data arranged like this. If you can rearrange the data with less tedium (you have not really said where the data are coming from, so I don't know if there is something in getting from the source into the spreadsheet that you could do differently), then the chart will be easier to create. Since there is no built in way to add trendlines to multiple series, I would probably calculate the data for the trendlines in the spreadsheet and not use the trendline feature.

    In the attached sheet, not knowing where the data came from, I started with the "awkward" table in Chart. Then I:

    1) Built a list of company names without the blank rows between. I used autofilter then copy/paste for this, but I'm sure there are other ways as well.
    2) Added a column for row #, which is simply a list of odd numbers.
    3) Using INDEX() functions, I extract start year, end year, start value, and end value for each company from the list into columns.
    4) With the years and values in adjacent columns (see spreadsheet), I can use a LINEST() function to find "slope" and "intercept" for any simple linear function. Your chart shows some linear and some logarithmic, but I wasn't sure how you determined which to use, so I just used a logarithmic (y=m*log(x)+b) for all companies.
    5) With slope and intercept, I can compute the value at any year between start and end with a simple formula (see columns J and to the right).
    5a) Enter years in row2
    5b) Enter the formula in rows 3+. If you pay attention to relative and absolute references, you should be able to enter the formula once, then copy/paste/fill into the rest of the block.
    6) I add a copy of column A in column I for convenience in creating the chart.
    7) Select I2:end and insert scatter chart. Format chart elements as desired.

    My older version cannot show all the data series on the chart, so my chart only shows a few of the data series. I would note that 350 data series is going to be a very busy chart, and I am pessimistic that it will be very useful. Perhaps you intend to include a filter step so you can limit the chart to a few data series or something. That's how I would arrange the data in order to minimize the tedium needed to create the chart.

    Of course, all of that assumes that you are allowed to rearrange the data and are not artificially required to work with the data as you show it in Chart.

    Does that help any?
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    03-28-2021
    Location
    Italy
    MS-Off Ver
    365
    Posts
    9

    Re: How can I make a diagram like this?

    Yes, thank you very much, that helps me a lot. I like the created chart very much

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA- Copy cells incl. Diagram and update diagram reference
    By Julian91 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-22-2018, 07:03 PM
  2. [SOLVED] Excel Diagram: bubble diagram with dynamic quadrants
    By meiselsan in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 04-27-2015, 08:26 AM
  3. How to make a diagram of this?
    By Kenny_Lundbeck in forum Excel General
    Replies: 4
    Last Post: 04-23-2014, 07:06 AM
  4. How to make a functional Venn diagram bulls eye chart for accuracy
    By phillips076 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 12-31-2013, 11:54 AM
  5. vectors end to end to make network diagram
    By simonsmith in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 03-21-2010, 01:54 PM
  6. how do i make a pxy diagram?
    By dwe1981 in forum Excel General
    Replies: 0
    Last Post: 01-23-2006, 05:10 PM
  7. how to make a schematic diagram
    By EEfury in forum Excel General
    Replies: 1
    Last Post: 10-13-2005, 02:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1