Trying to calc a average

[Larry]

I have a spread sheet that has a series of rows.

I need to calculate a moving average of the last 3 cells that are NOT
"e".

I can get it to work with sumif and countif by placing a row below
that is e)estimate or a)ctual but how do I get the function to only
go back for the last 3 actuals. In the sample below the last one is
averaging 4 sets of number I and Do not want it to pick up the first
15.

10 15 20 33 25 22 30
e a e a e a a


0 15 15 24 24 23.333 28.33

With programing I would set:

Count = 1
Value = 0
Est = " "

While count < 3
Add 1 to count giving count
If Est = "a" then
Value = Value + Value
Count = Count + 1
Else
offset one more column (backup another column)
Endwhile

Average = value / count

Move to the next cell and start over.

Any ideas would be appreciated.

 

[Don Guillett]

think you may find your answers here
http://tinyurl.com/4fvwz

 

[Jason Morin]

With letters in row 2 (starting in col. A) and numbers in
row 1 (starting in col. A), try:

=SUMIF(IV2:OFFSET(A2,,LARGE(IF(2:2="a",COLUMN(1:1)),3)-
1),"<>e",IV1:OFFSET(A1,,LARGE(IF(2:2="a",COLUMN(1:1)),3)-
1))/3

Array-entered, meaning press ctrl/shift/enter. XL will
place {} around the formula.

HTH
Jason
Atlanta, GA