Excel masters,
I have a question, in an excelsheet (office xp), I have the following data:
department score date moving average last 3 rows under
condition department a or b
....
a 8 1 jan 2008 ???
a 9 1 feb 2008
b 8 2 feb 2008
a 7 3 mar 2008
b 4 4 april 2008
a 5 may 2008
...and so on
The scorecell may be null.
How do I show the moving average in each row, (over the last six months or 3
rows with condition a or b). The problem is the condition department.
I looked at daverage but that doesnt work because the selection is not
contigious with the title.
Another solution is :
=SUMIF(OFFSET($A3;0;0;-3;1);2;B1:B1)/(COUNTIF(OFFSET($A3;0;0;-3;1);2))
This works but the scores may be null so the average is not good (wrong
count) if there is a null cell and the function countblank is not
conditional.
Excel 2007 has average.if but I work with xp
Alternative:
=IF(COUNTBLANK(OFFSET($B3;0;0;-3;1))=0;SUMIF(OFFSET($A3;0;0;-3;1);2;B1:B1)/(COUNTIF(OFFSET($A3;0;0;-3;1);2));"---").
This works but makes a "hole" in three rows if there is one empty cell.
Is there anothe handy function or solution, the analysis add-in doesnt work
either
Greetings and thanks, Norbert