# Thread: Moving Average with text value (2K3)

1. ## 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?

2. ## 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. ## 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.

#### Posting Permissions

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