Results 1 to 3 of 3
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Moving Average with text value (2K3)

    I am using the Moving Average Tool in the Data Analysis Toolpak of Excel and have come across a mild irritant.

    I have a list of numbers that I want to calculate the averages for but in some of the cells in the range I have a text value and therefore Moving Average bugs out, however, I have got around this issue by adding an extra column and added an If statement along the lines of =IF(C2="Stuff",0,C2) which returns a 0 and then I can use the Moving Average without error.

    The question, is there a way of using moving average with text comments negating the use of an extra column or is there a working formula?
    Jerry

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Moving Average with text value (2K3)

    Do you want to completely ignore the text values? If so, you can simply create the average formula manually in the first appropriate cell, then fill down.
    Or should the formulas always take the average over the same number of numeric values, i.e. expand the range if there are text values in between? That would be more complicated?

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Moving Average with text value (2K3)

    Thanks Hans,

    Whilst I was awaiting a reply I have stumbled upon that little ditty <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

    I think that as this is the basis of a Training Log I can manually do it using the IF statement method above to change the value of in a hidden column and use this formula to work the MA

    =IF(OR(ISERROR(AVERAGE(D17)),D7=0),"---",AVERAGE(D17))

    This is not perfect as I will have to slightly adjust the formula to fit the criteria for the Log.
    Jerry

Posting Permissions

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