averaging cells

[Ravens Fan]

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.99999999999999E+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

 

[Bernard Liengme]

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

 

[Ravens Fan]

No it didn't work. It still returns 351 to the cell.

 

[Bernard Liengme]

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)

 

[T. Valko]

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,MATCH(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!

 

[T. Valko]

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!

 

[Ravens Fan]

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".
DT4T50. 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$4H6,LARGE(IF($DH$4H6<>"",ROW($DH$4H6)-ROW($DH$4)+1),MIN(COUNT($DH$4H6),3))):INDEX($DH$4H6,MATCH(5000,$DH$4H6)))

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

 

[T. Valko]

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.

 

[Ravens Fan]

I'm sorry for the confusion. Thanks for all your help and suggestions. The
last formula you sent worked great.

 

[T. Valko]

You're welcome. Thanks for the feedback!