+ Reply to Thread
Results 1 to 4 of 4

Automate Chart Building or Number Generation

  1. #1
    Registered User
    Join Date
    12-08-2015
    Location
    Paris
    MS-Off Ver
    2010
    Posts
    3

    Automate Chart Building or Number Generation

    Hi All,

    I've tried Google in every combination I can think of with no luck. I'm not sure how to even phrase the problem, but I'm sure it has a solution.

    I have a business model with various inputs, the two most important of which are time and price (It's always about time and money). I want to create a chart with the Y-axis showing price and the X-axis showing time which draws a line at profit break-even. For example, if I set Price to $45, then break-even will be at 9 months. At $50, it will be at 10, etc. If I set Time to 6 months, break-even will be at a Price of $40. I would like to draw that line without having to make a manual input for each data point in the chart.

    If I could auto-generate a table of Time values for a series of Price inputs so that I could enter the prices I want for the Y axis, and I could generate the corresponding X values, that would be halfway to the chart.

    To find a break-even point now, I use goal seek to set the profit to zero by changing either Price or Time. I just don't want to have to keep doing that over and over for each data point.

    I know that I could have had half the numbers I need in the time it's taken me to post this message, but every time other parts of the model change (cost, for example), then I have to start over, since it changes break-even points.

    Thanks for any help.

  2. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Automate Chart Building or Number Generation

    t = time (to break even)
    p = price

    t = f(p)

    So what is f ?

    Figure out what that is, write the formula in the second column and dropfill, and you're done, no?
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.

  3. #3
    Registered User
    Join Date
    12-08-2015
    Location
    Paris
    MS-Off Ver
    2010
    Posts
    3

    Re: Automate Chart Building or Number Generation

    Thanks Ben,

    If I understand your suggestion, I think my issue is that the function is the rest of the spreadsheet. In the equation t = f(p), the f is a large model with many more variables. I would like to avoid condensing all of them into one formula (assuming that I would capable of doing so). Apologies in advance if I misunderstood your solution.

    If I could automate the number generation of p, holding t constant, then I would be able to have two columns of numbers for the chart.

    Or, perhaps there's a way to put a series of numbers in a column under the heading "Time" (say months 1 - 36) and just goal seek for Profit = 0 by changing Price, each time feeding the next number (2, 3, etc.) into the formula. Can that be done with a macro?

    Thanks for your quick reply and interest in helping.

    Jeff

  4. #4
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Automate Chart Building or Number Generation

    Can you post a copy of your spreadsheet with (1) sensitive data pulled out and (2) an indication of what you approximately want?

    I just don't understand what you have or what, precisely, you're asking for.

+ 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. Please help automate field generation on second sheet
    By hoolicanddd in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-05-2015, 08:55 PM
  2. Chart generation VBA
    By Hitmonlee in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-03-2013, 02:39 AM
  3. Automate Excel Formulas for monthly report generation
    By lakshmi123 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-08-2012, 05:23 PM
  4. How to automate the date range when building Time-series charts in VBA
    By vbanewbie1233 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-14-2012, 08:11 AM
  5. Automate graph generation based on [variable] time
    By sc30317 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-29-2011, 12:17 PM
  6. Automate report Generation
    By Epscan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-18-2009, 05:20 PM
  7. Replies: 2
    Last Post: 02-04-2009, 02:55 PM

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