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

1. ## 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?  Register To Reply

2. ## 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?  Register To Reply

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

Whoops repost  Register To Reply

4. ## 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. Originally Posted by Crooza 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?  Register To Reply

5. ## 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  Register To Reply

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

Oh. And welcome to the forum....  Register To Reply

7. ## 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. Originally Posted by Crooza 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  Register To Reply

8. ## 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.  Register To Reply

9. ## 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! Originally Posted by Crooza 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.  Register To Reply

10. ## 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  Register To Reply

11. ## 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.  Register To Reply

12. ## 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.  Register To Reply