Averaging Last 6 Months

[streetboarder]

I have a cell (c3) that it titled 6 month average where I need the
formula.

Then to the right I have cells for given months:
d3 = July 05
e3 = August 05
y3 = April 07

In those cells I have the my number I need to average. In this case it
is total deposits. I need a average formula that does the following:

Data Range: D3:CC3
I need to average that last 6 months of data for reporting purposes
while ignoring blanks and anything over 7 months. Is this possible?

Right now the person is having to change the formula each month, delete
and rename the column headers, etc. What I would prefer is that they
simply hide the given column that is no longer averaged so that it is
still there but not included.

Please let me know if this is possible.
Josh

 

[Don Guillett]

try this where j2 is the LAST of the six to average.
=AVERAGE(OFFSET(J2,,,,-6))

--
Don Guillett
SalesAid Software
(e-mail address removed)
"streetboarder" <[email protected]>
wrote in message
news:[email protected]...

 

[Domenic]

Try...

=AVERAGE(INDEX(D3:CC3,LARGE(IF(D3:CC3<>"",COLUMN(D3:CC3)-COLUMN(D3)+1),6)
):INDEX(D3:CC3,MATCH(9.99999999999999E+307,D3:CC3)))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

streetboarder

 

[streetboarder]

Domenic,

When I enter the information it is returning a #value! error. Any
ideas?

Can you please explain the forumlua below so I can have a better
understanding of what it is trying to do. It looks close but I'm not
there yet.

Thanks,
Josh

 

[streetboarder]

Domenic,

When I enter the information it is returning a #value! error. Any
ideas?

Can you please explain the forumlua below so I can have a better
understanding of what it is trying to do. It looks close but I'm not
there yet.

Thanks,
Josh

 

[Domenic]

Make sure that you confirm the formula with CONTROL+SHIFT+ENTER, not
just ENTER. If this doesn't help, can you post the exact formula you're
using?

streetboarder

 

[streetboarder]

Ok. After a little more information from the form user I have found tha
I need to do the exact same thing but ingonore the last cell wit
information in it (most recent month).

Example:
Jan, Feb, Mar, April, May, June, July,

I need to ingnore July and average Jan - June. Any chance you can hel
me Domenic? I have done the same in the past but have had to use macro
to change it each time - I wasn't sure it could be done in a formul
until now so thanks!

Also, once I get the above to work I have another cell "C" that has th
following if statement:

=IF(D6>1.5*K6+10000,"Reportable",If(D6<.5*K6-10000,"Reportable","no"))

D6 refers the average formula we are working on. K6 refers the the mos
current month. In the case about it would be July. Is there a way I ca
reference this cell without having to do create a macro to change it?

I need to look in the same range. E6:CC6

Thanks for all the help!!
Jos

 

[Domenic]

For the first part, you can use the following formula instead...

=AVERAGE(INDEX(D3:CC3,LARGE(IF(D3:CC3<>"",COLUMN(D3:CC3)-COLUMN(D3)+1),7)
):INDEX(D3:CC3,MATCH(9.99999999999999E+307,D3:CC3)-1))

....confirmed with CONTROL+SHIFT+ENTER.

For the second part, you can use the following formula to give you the
last numerical value in a range...

=LOOKUP(9.99999999999999E+307,Range)

Hope this helps!

streetboarder

 

[streetboarder]

Thank you so much!

Out of curiosity what does "9.99999999999999E+307 mean?

Thanks again!
Josh

 

[Domenic]

Thank you so much!

You're very welcome! Glad I could help!

Out of curiosity what does "9.99999999999999E+307 mean?

It's the largest number that Excel recognizes. Have a look at Aladin's
contribution in the following thread for additional information...

http://www.mrexcel.com/board2/viewtopic.php?t=105725

Cheers!