Macro help

  • Thread starter Thread starter wilder
  • Start date Start date
W

wilder

Here's my question:

I have an interest rate graph that I would like to update every day.
However, it is a waste of time and a pain to update it the way I do
now, so I am trying to create a macro that will make my life much
easier.

Here is a simplified version of what I am trying to do:

Say for instance I have interest rates for the first five days of the
week in cells A1:A5. When I add rates for the sixth day, I need to
update my various averages. Say, a moving two-day average which
calculates the average of A5:A6 instead of yesterdays A4:A5 or a
continuous weekly average for A1:A6 instead of yesterdays A1:A5.

So, I tried to record a macro that would perform the simple task of me
MOVING DOWN the (I don't know exactly what to call it, so I am going
to call it the blue highlight outline) blue highlight outline one cell
for the 2-day moving average and EXTENDING the blue highlight outline
by one cell for the continuous weekly average, i.e., blue highlight
outline now outlining A5:A6 instead of A4:A5 for the 2-day moving
average and outlining A1:A6 instead of A1:A5 for the continuous
average. However, when I tried to record my macro by performing these
functions (moving the blue highlight outline down one and extending it
by one), the macro only moves them to the specified cells, it doesn't
move the blue highlight outline down another cell, i.e., once I play
the macro for day seven, it won't move the 2-day moving average from
A5:A6 to A6:A7, it just keeps it at A5:A6 like I said before...any
suggestions. If so, please copy your suggestion in an email to
(e-mail address removed).

Thanks,

Wilder
 
Hi wilder
I think you don't have to use macros for that.
1. To get the cell with the last entry use COUNTA
2. To calculate your 2 day average use the following (assumption: your
data is in column A)
=SUM(OFFSET($A:$1,COUNTA($A:$A)-1,0,-2))/2
3. If you need a dynamic chart (moving with your data entry) have a
look at the following sites
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html
http://peltiertech.com/Excel/Charts/Dynamics.html
The also make use of the OFFSET and COUNTA functions (together with
defined names) to create dynamic charts

HTH
Frank
 
Frank, Please recomment me a textbook/reference/menual on macro
code/syntax writing. Thanks
 
Back
Top