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

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

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

Odd, it worked for me. With ISBLANK(A1) you must but the formula in cell B1.
Feel free to send me a copy of your workbook (private email, not to the
newsgroup)
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"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

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

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

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

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

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

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