+ Reply to Thread
Results 1 to 12 of 12

Graph a curve, then enter data to generate new similar curve

  1. #1
    Registered User
    Join Date
    11-01-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    5

    Lightbulb Graph a curve, then enter data to generate new similar curve

    Hello all! I'm new to this forum and I'm kind of a hack in excel. I don't really know what I'm doing, but somehow I've been able to get it to do pretty much anything I've ever really wanted. Until now!


    So what I would like to do is this:

    1) I would like to use existing data to plot a graph curve. It will have the start date through end date of a production run, as well as number of units made each day.

    This part I figured out: the next I can't seem to get.

    2) I would like to enter a different size production run, and a different number of total units to create a new graph. This graph's shape is exactly proportional to the first.

    * In other words, the second graph input is only the number of units and number of days-- I want to generate a rough prediction of how it will fluctuate assuming it will look like the first graph.

    Any guesses?

  2. #2
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Graph a curve, then enter data to generate new similar curve

    Do you have some sample data? Am I right in assuming that if for example the last production run was 10 and distributed over say 5 days but with the following distribution
    Day 1 1 unit
    Day 2. 1 unit
    Day 3 3 units
    Day 4. 2 units
    Day 5. 3 units

    And the next production was 20 units over the same number of days you want it distributed in the same proportion so
    Day 1 2 units
    Day 2. 2 units
    Day 3 6 units
    Day 4. 4 units
    Day 5. 6 units

    But if it was distributed over say 10 days it would then be say the same shape but about half of the above on each day?

    Is this right?

  3. #3
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Graph a curve, then enter data to generate new similar curve

    Whoops repost
    Last edited by Crooza; 11-02-2013 at 02:00 AM. Reason: Repost

  4. #4
    Registered User
    Join Date
    11-01-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Graph a curve, then enter data to generate new similar curve

    Crooza - Thanks for responding!

    The sample data and the assumptions you made are exactly correct.


    Quote Originally Posted by Crooza View Post
    Do you have some sample data? Am I right in assuming that if for example the last production run was 10 and distributed over say 5 days but with the following distribution
    Day 1 1 unit
    Day 2. 1 unit
    Day 3 3 units
    Day 4. 2 units
    Day 5. 3 units

    And the next production was 20 units over the same number of days you want it distributed in the same proportion so
    Day 1 2 units
    Day 2. 2 units
    Day 3 6 units
    Day 4. 4 units
    Day 5. 6 units

    But if it was distributed over say 10 days it would then be say the same shape but about half of the above on each day?

    Is this right?

  5. #5
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Graph a curve, then enter data to generate new similar curve

    Ok. I'm out for the next few hours. Will have a look when I get home. Is the first curve always the same number of days or does that vary too
    Last edited by Crooza; 11-02-2013 at 06:18 PM. Reason: Typo

  6. #6
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Graph a curve, then enter data to generate new similar curve

    Oh. And welcome to the forum....

  7. #7
    Registered User
    Join Date
    11-01-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Graph a curve, then enter data to generate new similar curve

    Thanks again

    I plan on choosing about 5 projects of different types and when we get a new job, setting up the schedule so that it runs similar to one of those. These are our "standard" jobs.

    Once we choose those jobs we will enter the data for the first curve manually. Each of the 5 options will vary in size, from approximately 10-120 days. These 5 curves will not change.

    Quote Originally Posted by Crooza View Post
    Ok. I'm out for the next few hours. Will have a look when I get home. Is the first curve always the same number of days or does that vary too

  8. #8
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Graph a curve, then enter data to generate new similar curve

    proportional graph shape.xlsx

    Hi. How about this? It allows you to enter days and output on the left then on the right set the total days of production and the output. The second curve coordinates will be automatically generated based on teh curve first curve coordinates and the days and total output required. Depending on the relationship between the first and second curves you might get a little bit of error because in some instances it's producing fractions of products. Not sure if this is acceptable or whether only whole products can be produced.

    I've allowed for up to about 100 days on both curves. I note that you might want to go to 120 days. If we're on teh right track here I'll tidy that up for you.

    Give it a try and let me know if this is heading in the right direction.

  9. #9
    Registered User
    Join Date
    11-01-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Graph a curve, then enter data to generate new similar curve

    You are awesome!!

    This is exactly what I was trying to do. I see in your formula vlookup and the "if" function - brilliant! Tomorrow when I have a little more time I will give it a closer look. Thanks for the help!

    Quote Originally Posted by Crooza View Post
    Attachment 275321

    Hi. How about this? It allows you to enter days and output on the left then on the right set the total days of production and the output. The second curve coordinates will be automatically generated based on teh curve first curve coordinates and the days and total output required. Depending on the relationship between the first and second curves you might get a little bit of error because in some instances it's producing fractions of products. Not sure if this is acceptable or whether only whole products can be produced.

    I've allowed for up to about 100 days on both curves. I note that you might want to go to 120 days. If we're on teh right track here I'll tidy that up for you.

    Give it a try and let me know if this is heading in the right direction.

  10. #10
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Graph a curve, then enter data to generate new similar curve

    Great. I'm glad it's what you wanted. Test it out a bit as I only spent 20 or so minutes on this and ran some very quick tests. If you find any bugs let me know

  11. #11
    Registered User
    Join Date
    11-01-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Graph a curve, then enter data to generate new similar curve

    Hello again- I've looked it over and it's really good. I'm only a little confused with the formula for the second graph. It includes C10, which is day "zero" on the first graph.

  12. #12
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Graph a curve, then enter data to generate new similar curve

    The output table uses the first table as a lookup. Because the first table starts at day 1, any lookup value less than 1 is outside the lookup range. This isn't a problem if the number of days in the second table is shorter than the first table because it compresses the output values but when the second table is longer than the first table then the 'stretching' of the values requires it to look at fractions of a day and this results in it wanting to look at values less than 1 day. For example if table 1 is 10 days and table 2 is 20 days. I'm using day 1 in table 2 is equal to 0.5 days in table 1 to get the nearest approximation to output in table 1. Or if table 2 is 30 days then I'd be lookiing for 0.33 days in table 1 to get the nearest output. To get around this I created day 0 and set its ouput to whatever you enter for day 1 output. That means that when it looks up the fractional days below 1 it gets day 1 values rather than return an error becasue it couldn't find anything. Hope I've explained myself well.

+ 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. Normal distribution curve / Bell curve
    By LAG1 in forum Excel General
    Replies: 0
    Last Post: 05-24-2012, 07:20 AM
  2. Generate an S curve
    By GregFoad in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-31-2011, 09:35 PM
  3. Graph distribution curve
    By harryhu in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 12-13-2009, 04:29 PM
  4. Fitting a curve similar to capacitor charging current.
    By mcgradys in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 11-22-2005, 02:27 PM
  5. curve fitting a charging capacitor type curve
    By mcgradys in forum Excel General
    Replies: 4
    Last Post: 11-15-2005, 08:50 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