Need help with excel prediction dragging for a repetitive formula

[GiovanniSegar]

When you put in a series of numbers in sequential cells then drag from the corner of the cell, Excel will see the pattern and predict the next few numbers for you.

But in more complex cases, Excel completely misses the pattern. For example, this formula:

=GETPIVOTDATA("Sum of NetSpentFirst30Days",$A$3,"AccountCreationDate",1,"Years",2016)/GETPIVOTDATA("Count of UserID",$A$3,"AccountCreationDate",1,"Years",2016)

I want to increase the 1 to a 2, then a 3, etc in sequential cells. Right now, I need to manually change both 1's for every cell. Is there a better way to do this? The only thought I have is that I could piece together the formula with a concatenate formula, but that would take a while to figure out with all the quotes I'd have to escape to make it work.

Any ideas for complex pattern prediction? Is there an excel function I'm missing that lets me tell excel which number to increment?

 

[thisoldman]

Use ROW() or COLUMN() functions to do the counting.

One way to do it, meant for copying down the column:

=GETPIVOTDATA("Sum of NetSpentFirst30Days",$A$3,"AccountCreationDate",ROW(A1),"Years",2016)/GETPIVOTDATA("Count of UserID",$A$3,"AccountCreationDate",ROW(A1),"Years",2016)

You can use other variations for counting (depending on where your column of formulas start): ROW(1:1), ROW()-1, ROW()-ROW($N$1)

 

[GiovanniSegar]

Use ROW() or COLUMN() functions to do the counting.

One way to do it, meant for copying down the column:

=GETPIVOTDATA("Sum of NetSpentFirst30Days",$A$3,"AccountCreationDate",ROW(A1),"Years",2016)/GETPIVOTDATA("Count of UserID",$A$3,"AccountCreationDate",ROW(A1),"Years",2016)

You can use other variations for counting (depending on where your column of formulas start): ROW(1:1), ROW()-1, ROW()-ROW($N$1)

Perfect, thank you.

I wish Excel had a way to manually select a section to predict so that all the extra text wouldn't confuse excel. That way you could do it with months as well, for example. But for now, this works.