How to use sum/average function on 3 consecutive values

[jay.kum]

Hi,

This may sound like the easiest question, but believe me its not. I'm
strugling with it for quite some time now.

Let me explain what is that I want. I have data for several customers
on their daily order values. Customers in rows and days in columns.
Say

day1 day2 day3 day4
day5 day6
cst 1 11 12 13
14
cst 2 11 12
13 15 16
cst 3 11 12
14 15 16
cst 4 11 12 13
14

Basically, I need the sum of their last 3 consecutive order values,
starting from most recent date (from right). So for cst 1 it would be
(12+13+14) for cst2: (11+12+13), cst3: (14+15+16) and cst4:
(12+13+14).

I suppose array formula should be of help but not sure how to do it.
Any idea can I get this done?

 

[Guest]

Consider putting the most recent data on the left rather than the right. It
does mean inserting data rather than just tacking it on the end.

But this way summing or averaging the last three sample is very easy.

 

[Bernard Liengme]

Hard to do with just Excel but this (only partial test) UDF seems to work

Function Last3(myrange)
rangecount = myrange.Count
For j = rangecount To 1 Step -1
If myrange(j) > 0 Then
mysum = mysum + myrange(j)
mycount = mycount + 1
Else
If mycount < 3 Then
mycount = 0
mysum = 0
End If
End If
If mycount = 3 Then Exit For
Next j
If mycount = 3 Then
Last3 = mysum / 3
Else
Last3 = "N/A"
End If
End Function

Need help with VBA? David McRitchie's site on "getting started" with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm
best wishes