Here are a couple of functions
Function FMA(i)
If TypeName(i) = "Range" Then
If i.Cells.Count = 1 Then
If i.Row >= 6 Then
FMA = Application.Average(i.Offset(-4).Resize(5))
Else
FMA = ""
End If
Else
FMA = "Formula only applies to single cells"
End If
Else
FMA = "Formula requires a cell reference"
End If
End Function
Function RSI(i)
If TypeName(i) = "Range" Then
If i.Cells.Count = 1 Then
With Application.Caller
If .Row <= 2 Then
RSI = ""
ElseIf i.Value > i.Offset(-1, 0).Value Then
RSI = "up"
ElseIf i.Value < i.Offset(-1, 0).Value Then
RSI = "down"
Else
RSI = ""
End If
End With
Else
RSI "Formula only applies to single cells"
End If
Else
RSI = "Formula requires a cell reference"
End If
End Function
wher you call with =FMA(E2) in row 2, and =RSI(D2) in row 2.
But you can just use worksheet formulae for both
FMA: =IF(ROW()<=5,"",AVERAGE(OFFSET(E6,-4,,5)))
RSI: =IF(ROW(D2)>2,IF(D1>D2,"down",IF(D1<D2,"up","")),"")
both formulas given as in row 2.
To get your 10 day figure, just use
=100-100/(1-COUNTIF(G:G,"up")/COUNTIF(G:G,"down"))