How to use sum/average function on 3 consecutive values

J

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?
 
G

Gary''s Student

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.
 
B

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top