Possibilities to extend GROWTH function to avoid manual edit of formulas?

thrill_Bill

New Member
Joined
Feb 23, 2023
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hello friends,

I am using GROWTH to predict exponential growth with existing data, but I was wondering, do I have to have edit the formula for the corresponding units and the predicted units, everytime I have more actual data for the past month? Are there maybe any ways to add the IF function here?

For January and February I have units of 10000 and 2000 accordingly and my formula for corresponding units would be =(B2:B3;A2:A3) and for predicted units =GROWTH(B2:B3;A2:A3;A4:A12). Now if I have existing data for March, I would have to edit both formulas and this would continue for every upcoming month. Are there any ways to avoid this step?

Thank you.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Oh yes, but you may not like it. Have you encountered dynamic ranges?
First, I'm assuming a years monthly data so it row 13 not 12.
OFFSET in teh 5 variable form gives you a range (base cell, rows down to look, cols across to look, how many rows in the range, how many columns in the range.
You need to use OFFSET and COUNT/COUNTA - COUNTA is more reliable it includes data in the range.
Say your growth starts in col 13. You would use
=GROWTH(OFFSET(B$2,0,0,COUNTA(B$2:B$13,1),1);OFFSET($A$2,0,0,COUNTA(B$2:B$13,1);offset($A$2,COUNTA(B$2:B$13),0,13-COUNTA(B$2:B$13),1)
What that says is GROWTH(
start at B2​
Add+0 rows down to look at,​
Add+0 columns to look at,​
Relative to B2 the number of rows with data in column B,​
1 column wide​
Then
start at A2​
Add+0 rows down to look at,​
Add+0 columns to look at,​
Relative to B2 the number of rows with data in column B,​
1 column wide​
Then
start at B2​
Add+the number of rows in column B with data down to look beyond,​
Add+0 columns to look at,​
Relative to B2 the total number of rows less the number of rows with data in column B,​
1 column wide​
You may need to fiddle but I hope this helps you.

OFFSET is brilliant. It only works in open spreadsheets, but is can be used to define ranges to use aggregate functions on.

And lookup dynamic ranges once you've understood OFFSET, you can create pivot tables where you never need to change the source because it expands dynamically to fit the data.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top