+ Reply to Thread
Results 1 to 6 of 6

Change formula in a calculated column

  1. #1
    Registered User
    Join Date
    07-13-2012
    Location
    Uniontown, OH
    MS-Off Ver
    2010 Pro
    Posts
    9

    Change formula in a calculated column

    This could get confusing quickly, so I'll try to keep it as simple as possible.
    I have a table that I use to calculate a 15-day and 30-day moving average for stocks. Right now, when I change the price on my main worksheet it automatically adds a row to my table and all of my formulas automatically fill in and calculate in the new row.
    Now, I need to change the formula from a 15-day to a 10-day average. I changed the first formula from "=average(b2:b16)" to "=average(b2:b11)" which gives me a correct 10-day average. I then copied that cell all the way down the table. It gave me the correct 10-day average all the way down. But now, when I add today's stock price, it still creates the new row and all the columns auto fill, but in the column that I changed it is filling in with the old 15-day average formula, and the cells I copied the 10-day formula to have a triangle in the upper left corner saying "the cell is inconsistent with the column formula". I even tried removing the column and recreating it with the 10-day average, and that results in nothing being filled in for that column in the new row, but the other columns still work fine!

    The columns are about 200 rows long and I would rather not have to reenter all the data in a new table.

    Any thoughts?

  2. #2
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Change formula in a calculated column

    Hello jphalverson, and welcome to the forum.
    When you say:

    when I change the price on my main worksheet it automatically adds a row to my table and all of my formulas automatically fill in and calculate in the new row
    I assume you have a macro doing this for you. Is that macro putting the old formula into the table and filling it down? If so, you need to adjust the formula in the macro.

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Change formula in a calculated column

    I would recommend you edit the macro to not insert specific formulas at all, rather it copy them from a specific row on your sheet. This way any edits you make on sheet itself regarding the formulas, the macro will simply copy those, so it's self-updating, so-to-speak.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Change formula in a calculated column

    Hi jp and welcome to the forum,

    In Excel 2010 tables got a little smarter. This could be read as confusing also.
    Read http://blogs.office.com/b/microsoft-...xcel-2010.aspx

    1. Do you have VBA code behind your workbook that needs to be changed to do the 10 day instead of the 15 day? If you do you would need to change the VBA to get what you want.
    2. Do you really have your data formatted as a TABLE. If you do then new rows added to the table should calculate like the whole column is calculated.
    3. Try inserting a row at the bottom of the table instead of adding one after the last row. This would insure that new row is part of the table and Excel will treat it accordingly.

    Other readings: http://www.databison.com/index.php/t...ulas-in-excel/


    I hope this helps.
    Last edited by MarvinP; 07-14-2012 at 10:12 AM.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  5. #5
    Registered User
    Join Date
    07-13-2012
    Location
    Uniontown, OH
    MS-Off Ver
    2010 Pro
    Posts
    9

    Re: Change formula in a calculated column

    I'm using VBA with a change event on my main worksheet as follows: [remarks follow each line]

    Please Login or Register  to view this content.
    Upon changing cell I7, a new row is added to the table, the date and new stock price are put into column A and B and the formulas in columns D and E are inserted and update with the appropriate 15 and 30 day average, incrementing the rows used for the calculation. When I changed the 15 day formula to a 10 day formula and copied it down the column, everything still worked as before, but in the new row, the 10 day column inserted the old 15 day formula. I also got the "error" that the formulas that I copied were inconsistent with the column formula.

    My real question is: How do you change the column formula for a column in a table so when the table auto expands it inserts the new formula?
    Last edited by Cutter; 07-14-2012 at 12:01 PM. Reason: Added code tags

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Change formula in a calculated column

    Hi jp,

    I guess we need to see a sample workbook with the code and the table to understand the question. I don't know what formulas you have in the table or in your VBA. I'd also need to see the table boundaries and how you are adding the new row. All these could be part of the answer.

    To attach a sample workbook, click on "Go Advanced" below the message area and then on the Paper Clip Icon above the message area.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1