You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Ravens Fan" wrote in message
...
I'm sorry for the confusion. Thanks for all your help and suggestions. The
last formula you sent worked great.
--
Baltimore Ravens


"T. Valko" wrote:

I'm a bit confused!

You say the data to average is in column DT but your formula references
column DH.

The reason you're getting an error in the first cell is because your
referencing 3 cells.

Here's a screencap using the original data you posted starting in cell
DT4.
The formula is entered in cell DU4 and copied down.

http://img502.imageshack.us/img502/775/avglast3zj7.jpg

Here's a tip when posting a question: use REAL ranges and REAL data in
explaining what you have and what you want. When you use made-up
ranges/data
that's what we base our replies on and this can lead to problems on both
ends, (and usually does!) in our understanding of the question and when
you
try to implement our suggestions.

--
Biff
Microsoft Excel MVP


"Ravens Fan" wrote in message
...
Thanks. I new it could be shortened. I took the formula off of one of
the
other disscussions and just manipulated a few things, that's why I have
the
9.999etc in there. Thanks for the tips. I'm just learning excel and I
appreciate all the help this group gives.

I'm getting a #REF error when I use the last formula you sent:
This is the actual code I'm using. Column "DT" is the column I'm
looking
at
to see production and average out 3 months of production. Column "DU"
is
my
answer. There is a specific range that I'm looking at in column "DT".
DT4:DT50. There is more data further down that I have to start
averaging
over
again.

Here's the formula that I'm using:

=AVERAGE(INDEX($DH$4:DH6,LARGE(IF($DH$4:DH6<"",RO W($DH$4:DH6)-ROW($DH$4)+1),MIN(COUNT($DH$4:DH6),3))):INDEX($DH$ 4:DH6,MATCH(5000,$DH$4:DH6)))

DT DU
366 #REF
337 351
351
351
351
351
351
323 342
331 330
358 337
356 348
348
300 338
338
306 321
321
321
321
321
321
326 311
311
311
311
311


--
Baltimore Ravens


"T. Valko" wrote:

Improvement:

We can eliminate all this junk:

INDEX(A$1:A1,MATCH(1000,A$1:A1))

Now the formula becomes:

=IF(A1="","",ROUND(AVERAGE(INDEX(A$1:A1,LARGE(IF(A $1:A1<"",ROW(A$1:A1)-ROW(A$1)+1),MIN(COUNT(A$1:A1),3))):A1),0))

And, as I noted in my other reply, if the range does in fact start on
row
1
we can eliminate this junk:

-ROW(A$1)+1

Now the formula becomes:

=IF(A1="","",ROUND(AVERAGE(INDEX(A$1:A1,LARGE(IF(A $1:A1<"",ROW(A$1:A1)),MIN(COUNT(A$1:A1),3))):A1), 0))

Startin to look pretty good!

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this (array entered):

=IF(A1="","",ROUND(AVERAGE(INDEX(A$1:A1,LARGE(IF(A $1:A1<"",ROW(A$1:A1)-ROW(A$1)+1),MIN(COUNT(A$1:A1),3))):INDEX(A$1:A1,MA TCH(1E10,A$1:A1))),0))

Copy down as needed.

A couple of things:

Your 2nd result is 351. The unrounded average is 351.5. Rounded it's
352.

If your actual range does in fact start in row 1 and you never
expect
to
insert rows at the top of the range you can eliminate this portion
of
the
formula:

-ROW(A$1)+1

All your numbers are "relatively" small. In the MATCH function, you
can
use a more reasonable lookup_value: 9.99999999999999E+307. For
example,
if
the values will *never* be greater than 1000, use a lookup_value of
1000.
It's much easier to read than 9.99999999999999E+307 and it'll
shorten
the
formula by a few keystrokes!



--
Biff
Microsoft Excel MVP


"Ravens Fan" wrote in message
...
No it didn't work. It still returns 351 to the cell.
--
Baltimore Ravens


"Bernard Liengme" wrote:

I cannot get the same results that you have. But this should work
=IF(ISBLANK(A1),"", your_formula)
Of course it need to be array entered with SHIFT+CTRL+ENTER
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Ravens Fan" wrote in
message
...
Here is my dilemma. I am averaging only the last three times
something
has
been produced. Sometimes months go by with no production. On
those
months
I
donot want to show a average (since it did not run). The formula
I'm
using
averages three months of production and skips the blank months,
but,
it
still
shows and average. I'm hoping someone can manipulate my formula
and
show
me
how to put no data in months where there was no production.

This is the formula I'm using:

=AVERAGE(INDEX(A1:A100,LARGE(IF(A1:A100<"",ROW(A1 :A100)-ROW(A1)+1),MIN(C
OUNT(A1:A100),3))):INDEX(A1:A100,MATCH(9.999999999 99999E+307,A1:A100)))

Column "A" (actual Rate) is what I want to average and column
"B"
(3
month
avg) is the answer to my formula. As you can see, after the
first 2
months
of
data the average repeats it's self. Is there a way to make it
return a
blank
cell if no production was in that month. Example: Under "3 Month
Avg."
it
should read 366, 351 and then blank cells till you get to the
next
production
month of 323 out of column "A". I hope this isn't to confusing
and
any
help
will be greatly appreciated.
Note: These numbers will be charted, so I need to have blank
cells
not
0.

Actual Rate for 3 Mo Avg 3 Month Avg.
366 366
337 351
351
351
351
351
351
323 342
331 330
358 337
356 348
348
300 338
338
306 321
321



--
Baltimore Ravens