trend data, with twist

[Jeff Norville]

I think I'm making this harder than it is ... but that's never stopped
me from embarrassing myself here.

I've got some simple linear data to plot, but have been playing with
breaking this data into series over time -- so the linear trend can
shift based on occasional 'events' -- and trying to visualize this
effectively has proven a little challenging.

For example, take a year of savings deposits - enough to hit £1200 by
the end of twelve months, so £100 per month. If you wanted to play at
different scenarios mid-year, or end of Q3, so you could make your
savings goal -- but splurge on something midyear -- you might double
your deposits, with small adjustments for our miserable interest rates
these days. Might also have higher windfall deposits -- your
birthday's in April, or something like that.

I played at using a nearest-neighbors type of equation, get the
average of either adjacent cells, which requires turning off the
circular references alert... Could get around that by populating
formulas by VBA, sure. But this seems like something simple enough to
solve without getting into that.

So to address 'windfalls' I tried getting trickier using Range(Cell1,
Cell2).FormulaArray ... specifically I've got a years' data in a row;
I'm placing 'windfall' data in the row above, designating the year the
financial change happened, and this is the formula I assigned to the
array (hopefully r1c1 is legible for readers):
"=IF(OR(R[-1]C=0,ISBLANK(R[-1]C)), (RC[-1]+RC[1])/2, R[-1]C)"
Yes, we're in VBA now, with a 'watch' on Worksheet_SelectionChange, so
something like this:

If Not Intersect(Range(WS_RANGE), Target) Is Nothing Then
....make sure the ActiveCell isn't either of the "bookend" cells that
don't accept average values,
....then update a formula array to see if the cell above indicates a
trend change -- so grab it -- otherwise, take the average of adjacent
cells...
Range(Cells(iThisRow, iFirstCol +1), Cells(iThisRow, iLastCol -
1)).FormulaArray = "=IF(OR(R[-1]C=0,ISBLANK(R[-1]C)), (RC[-1]+RC[1])/
2, R[-1]C)"

Unfortunately this doesn't work one bit for me. Seems throwing the
formula into the array keeps it from recalculations.

Got any tips where I made some obvious wrong turn, aside from it being
Monday? (Barely, here.)

Regards,
Jeff

 

[Don Guillett Excel MVP]

I think I'm making this harder than it is ... but that's never stopped
me from embarrassing myself here.

I've got some simple linear data to plot, but have been playing with
breaking this data into series over time -- so the linear trend can
shift based on occasional 'events' -- and trying to visualize this
effectively has proven a little challenging.

For example, take a year of savings deposits - enough to hit £1200 by
the end of twelve months, so £100 per month.  If you wanted to play at
different scenarios mid-year, or end of Q3, so you could make your
savings goal -- but splurge on something midyear -- you might double
your deposits, with small adjustments for our miserable interest rates
these days.  Might also have higher windfall deposits -- your
birthday's in April, or something like that.

I played at using a nearest-neighbors type of equation, get the
average of either adjacent cells, which requires turning off the
circular references alert...  Could get around that by populating
formulas by VBA, sure.  But this seems like something simple enough to
solve without getting into that.

So to address 'windfalls' I tried getting trickier using Range(Cell1,
Cell2).FormulaArray ... specifically I've got a years' data in a row;
I'm placing 'windfall' data in the row above, designating the year the
financial change happened, and this is the formula I assigned to the
array (hopefully r1c1 is legible for readers):
"=IF(OR(R[-1]C=0,ISBLANK(R[-1]C)), (RC[-1]+RC[1])/2, R[-1]C)"
Yes, we're in VBA now, with a 'watch' on Worksheet_SelectionChange, so
something like this:

    If Not Intersect(Range(WS_RANGE), Target) Is Nothing Then
...make sure the ActiveCell isn't either of the "bookend" cells that
don't accept average values,
...then update a formula array to see if the cell above indicates a
trend change -- so grab it -- otherwise, take the average of adjacent
cells...
Range(Cells(iThisRow, iFirstCol +1), Cells(iThisRow, iLastCol -
1)).FormulaArray = "=IF(OR(R[-1]C=0,ISBLANK(R[-1]C)), (RC[-1]+RC[1])/
2, R[-1]C)"

Unfortunately this doesn't work one bit for me.  Seems throwing the
formula into the array keeps it from recalculations.

Got any tips where I made some obvious wrong turn, aside from it being
Monday?  (Barely, here.)

Regards,
Jeff

How about something simple. Now, when you want to chang for each
subseqent payment change the value. When you want it to go back to
another value simply change it back.....

=b2
=b3
=b4