Generate an exponential growth curve that will generate 600,000 units of sales in 70 days. Brandon sent in this question. It turns out that this is fairly easy to do using the GROWTH function in Excel.
Table of Contents
(0:00) Welcome & Problem
(0:21) Need to create an exponential growth curve that meets a certain size
(0:50) Use SEQUENCE function
(1:00) Add the word "Day" to each number using custom number format
(1:30) Using the GROWTH function with B14#
(1:50) Ctrl+* to select current region
(1:55) Inserting a chart
(2:25) Rejecting Goal Seek
(2:50) Formula to scale up using a constant
(3:45) Compounding the issue: four days are holidays with 0 sales
(4:10) Using COUNTA function
(4:30) Using WORKDAY.INTL function with no weekends syntax!
(5:40) Changing the Chart Data using Select Data
(5:52) Using F2 to edit in dialog box to prevent cell references being inserted
(6:45) Wrap-up
Table of Contents
(0:00) Welcome & Problem
(0:21) Need to create an exponential growth curve that meets a certain size
(0:50) Use SEQUENCE function
(1:00) Add the word "Day" to each number using custom number format
(1:30) Using the GROWTH function with B14#
(1:50) Ctrl+* to select current region
(1:55) Inserting a chart
(2:25) Rejecting Goal Seek
(2:50) Formula to scale up using a constant
(3:45) Compounding the issue: four days are holidays with 0 sales
(4:10) Using COUNTA function
(4:30) Using WORKDAY.INTL function with no weekends syntax!
(5:40) Changing the Chart Data using Select Data
(5:52) Using F2 to edit in dialog box to prevent cell references being inserted
(6:45) Wrap-up
Transcript of the video:
Learn Excel from MrExcel Podcast Episode 2335.
Create an exponential growth curve.
Hey, welcome back to MrExcel Netcast.
I'm Bill Jelen. Today's question was sent in by Brandon.
I love this one because it gave me a chance to use a function that Frankly, I've never really used before.
Brandon has a project that is going 10 weeks.
That's 70 days.
It's going to grow exponentially, he says, and it has to total 600,000 at the end. How can I create a curve that looks like this.
Where the total of those 70 points Is 600,000? I was, like whoa, no idea.
So now this solution requires dynamic arrays, which means you have Microsoft 365 and you have the functions like SEQUENCE, SORT, FILTER and so on.
If you don't have them, then you can do it, but it's going to require array formulas.
That's going to have to be a story for another day. So right here we say = SEQUENCE.
Of 70.
So 10 weeks times seven days.
That is going to generate the numbers one through 70 over there.
Just kind of an oddity here.
The way that Brandon had this set up.
He didn't have the numbers one through 70.
He had Day 1 through Day 70.
So what I am going to do is, I'm gonna select that whole range.
Press Ctrl+1 for Format Cells.
Go to Custom and instead of general, type in quotes, D A Y Space Quote Zero.
That will give us something that looks like text over there.
But in fact is really numbers.
Alright, so now the awesome solution.
To generate an exponential growth curve a beautiful function in Excel that I knew was there, but I've never used before, called GROWTH.
The GROWTH function and it turns out all we have to do is give it the numbers one through 70, so put a hashtag there and B14# points to the entire array.
And lo and behold, that generates an exponential curve.
Brandon said hey, it should look like this.
So let's take a look here.
I'll press CTRL+Asterisk to select all that data.
Insert, Chart, Line chart.
And if I shorten it up a little bit, sure enough, we have a curve that looks like Brandon's curve.
The only problem that we have.
Is it's not enough, right?
So right here if I just come here and do =SUM( of.
that hashtag.
Brandon wants 600,000 and we have 2573.1 right now.
Originally I used Goal Seek.
but Brandon says he has a bunch of these to do. I don't want to Goal Seek all the time.
I want to formula that's just going to do it.
All right. So I'm going to modify this formula.
I am going to say times one or times the constant, right there.
And that's the number that I changed with Goal Seek.
Putting in you know, it turned out I don't know it was 130 or something like that.
I said I wonder if I could make that constant just work.
So what I'm going to do here is I'm going to say I want the =SUM of the GROWTH of the SEQUENCE of 70.
Close Paren. Close Paren. Close Paren.
And that gives me 2573.1 and then my constant is simply going to be.
This is what we want.
600,000 / 2573.1.
And now my total comes up to 600,000.
How awesome is that right?
So the magic here is this GROWTH function.
Go read Excel help about GROWTH.
It does all kinds of things where you can specify known X's, known Y's.
Whether or not there should be a constant.
But it just so happens if you just need to generate an exponential growth curve.
It is a beautiful function, so I sent this off to Brandon.
Brandon says "that is awesome".
That's what he said in the evening.
Next day we get more information from Brandon's manager.
He says, well, hang on a second.
Out of these 70 days, there are four days that are Holidays.
We know there's not going to be any sales on those four days. Brandon comes back to me.
He says, Alright, well, how do we deal with four days that are Holidays?
Alright so here is my cheap way of doing this.
Here's the four Holidays that are going to have zero sales so.
We don't really have 70 days, we have Seven times the number of weeks minus the COUNTA of this Holiday table over here.
And now that's weird. They changed the format on me.
Very annoying. I'll change it back to a number.
So now in 66 days we need to get to 600,000.
And then what we are going to do over here; is instead of Day 1, 2, 3, 4, 5, 6.
I kind of have to leave those there.
What I'm going to do is I'm gonna put in today's date so I'm recording this on June 17th.
And then I'm going to say = WORKDAY dot INTL.
Start date is the date in the cell above me.
The number of days is 1.
Now check this out.
I want no weekends at all because we're going to work all 70 days except for those four Holidays.
So I'm going to use this alternate.
syntax where we have 7 digits starting on Monday, 1 means it is a weekend/ 0 means it's not a weekend.
So I'm just going to specify seven zeroes. 1, 2, 3, 4, 5, 6, 7. No weekends at all.
And then the thing to take advantage of here is the holiday argument. It says skip those holidays.
Right answer, wrong format.
I change that back to a date.
Double Click to copy that down.
And then all I need to do is make this chart be based on those dates instead of day one through.
Day 66. Select the chart.
On Chart Design, Select Data.
Come over here we're going to edit these cells that are currently pointing to A14 to B83 now.
I'm going to click here and change this to an A.
And my first inclination is to press the right arrow key.
But this dialog box is one of several in Excel that are super evil.
Because when I press the right arrow key it is going to start inserting cell references.
If I look down here in the lower left hand corner, it says that we're in a mode called Enter.
I'm going to press F2 and that's going to change me to Edit.
That will allow me to use the right arrow key safely to move over. Type A.
Press Delete to get rid of that B.
Click OK. Click OK.
Just to show you that it's working.
Change the chart type to a little column chart here.
Click OK and you'll see that there are the gaps.
So June 19th and Labor Day and the 4th of July and whatever other day is in there are being left out.
Great question from Brandon.
When this came in I was like, "Oh, I don't know. I have no idea".
But I was really happy to the way that this GROWTH function worked.
And again, it's so much easier thanks to dynamic arrays.
Microsoft 365. Is definitely the way to go.
If you like these tips please, down below the video.
Click Subscribe. And ring that bell.
Feel free to post any questions or comments down in the comments below.
My new book MrExcel 2020.
Seeing Excel Clearly has all kinds of great tips in it. Click the I in the top right hand corner.
I want to thank Brandon for sending the question and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Create an exponential growth curve.
Hey, welcome back to MrExcel Netcast.
I'm Bill Jelen. Today's question was sent in by Brandon.
I love this one because it gave me a chance to use a function that Frankly, I've never really used before.
Brandon has a project that is going 10 weeks.
That's 70 days.
It's going to grow exponentially, he says, and it has to total 600,000 at the end. How can I create a curve that looks like this.
Where the total of those 70 points Is 600,000? I was, like whoa, no idea.
So now this solution requires dynamic arrays, which means you have Microsoft 365 and you have the functions like SEQUENCE, SORT, FILTER and so on.
If you don't have them, then you can do it, but it's going to require array formulas.
That's going to have to be a story for another day. So right here we say = SEQUENCE.
Of 70.
So 10 weeks times seven days.
That is going to generate the numbers one through 70 over there.
Just kind of an oddity here.
The way that Brandon had this set up.
He didn't have the numbers one through 70.
He had Day 1 through Day 70.
So what I am going to do is, I'm gonna select that whole range.
Press Ctrl+1 for Format Cells.
Go to Custom and instead of general, type in quotes, D A Y Space Quote Zero.
That will give us something that looks like text over there.
But in fact is really numbers.
Alright, so now the awesome solution.
To generate an exponential growth curve a beautiful function in Excel that I knew was there, but I've never used before, called GROWTH.
The GROWTH function and it turns out all we have to do is give it the numbers one through 70, so put a hashtag there and B14# points to the entire array.
And lo and behold, that generates an exponential curve.
Brandon said hey, it should look like this.
So let's take a look here.
I'll press CTRL+Asterisk to select all that data.
Insert, Chart, Line chart.
And if I shorten it up a little bit, sure enough, we have a curve that looks like Brandon's curve.
The only problem that we have.
Is it's not enough, right?
So right here if I just come here and do =SUM( of.
that hashtag.
Brandon wants 600,000 and we have 2573.1 right now.
Originally I used Goal Seek.
but Brandon says he has a bunch of these to do. I don't want to Goal Seek all the time.
I want to formula that's just going to do it.
All right. So I'm going to modify this formula.
I am going to say times one or times the constant, right there.
And that's the number that I changed with Goal Seek.
Putting in you know, it turned out I don't know it was 130 or something like that.
I said I wonder if I could make that constant just work.
So what I'm going to do here is I'm going to say I want the =SUM of the GROWTH of the SEQUENCE of 70.
Close Paren. Close Paren. Close Paren.
And that gives me 2573.1 and then my constant is simply going to be.
This is what we want.
600,000 / 2573.1.
And now my total comes up to 600,000.
How awesome is that right?
So the magic here is this GROWTH function.
Go read Excel help about GROWTH.
It does all kinds of things where you can specify known X's, known Y's.
Whether or not there should be a constant.
But it just so happens if you just need to generate an exponential growth curve.
It is a beautiful function, so I sent this off to Brandon.
Brandon says "that is awesome".
That's what he said in the evening.
Next day we get more information from Brandon's manager.
He says, well, hang on a second.
Out of these 70 days, there are four days that are Holidays.
We know there's not going to be any sales on those four days. Brandon comes back to me.
He says, Alright, well, how do we deal with four days that are Holidays?
Alright so here is my cheap way of doing this.
Here's the four Holidays that are going to have zero sales so.
We don't really have 70 days, we have Seven times the number of weeks minus the COUNTA of this Holiday table over here.
And now that's weird. They changed the format on me.
Very annoying. I'll change it back to a number.
So now in 66 days we need to get to 600,000.
And then what we are going to do over here; is instead of Day 1, 2, 3, 4, 5, 6.
I kind of have to leave those there.
What I'm going to do is I'm gonna put in today's date so I'm recording this on June 17th.
And then I'm going to say = WORKDAY dot INTL.
Start date is the date in the cell above me.
The number of days is 1.
Now check this out.
I want no weekends at all because we're going to work all 70 days except for those four Holidays.
So I'm going to use this alternate.
syntax where we have 7 digits starting on Monday, 1 means it is a weekend/ 0 means it's not a weekend.
So I'm just going to specify seven zeroes. 1, 2, 3, 4, 5, 6, 7. No weekends at all.
And then the thing to take advantage of here is the holiday argument. It says skip those holidays.
Right answer, wrong format.
I change that back to a date.
Double Click to copy that down.
And then all I need to do is make this chart be based on those dates instead of day one through.
Day 66. Select the chart.
On Chart Design, Select Data.
Come over here we're going to edit these cells that are currently pointing to A14 to B83 now.
I'm going to click here and change this to an A.
And my first inclination is to press the right arrow key.
But this dialog box is one of several in Excel that are super evil.
Because when I press the right arrow key it is going to start inserting cell references.
If I look down here in the lower left hand corner, it says that we're in a mode called Enter.
I'm going to press F2 and that's going to change me to Edit.
That will allow me to use the right arrow key safely to move over. Type A.
Press Delete to get rid of that B.
Click OK. Click OK.
Just to show you that it's working.
Change the chart type to a little column chart here.
Click OK and you'll see that there are the gaps.
So June 19th and Labor Day and the 4th of July and whatever other day is in there are being left out.
Great question from Brandon.
When this came in I was like, "Oh, I don't know. I have no idea".
But I was really happy to the way that this GROWTH function worked.
And again, it's so much easier thanks to dynamic arrays.
Microsoft 365. Is definitely the way to go.
If you like these tips please, down below the video.
Click Subscribe. And ring that bell.
Feel free to post any questions or comments down in the comments below.
My new book MrExcel 2020.
Seeing Excel Clearly has all kinds of great tips in it. Click the I in the top right hand corner.
I want to thank Brandon for sending the question and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
