Averaging Last 6 Months
 

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


--
streetboarder
------------------------------------------------------------------------
streetboarder's Profile: http://www.excelforum.com/member.php...o&userid=30707
View this thread: http://www.excelforum.com/showthread...hreadid=511977

Averaging Last 6 Months
 

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

--
Don Guillett
SalesAid Software

"streetboarder"
wrote in message
news:streetboarder.236wpm_1139871300.8869@excelfor um-nospam.com...

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


--
streetboarder
------------------------------------------------------------------------
streetboarder's Profile:
http://www.excelforum.com/member.php...o&userid=30707
View this thread: http://www.excelforum.com/showthread...hreadid=511977

Averaging Last 6 Months
 

Try...

=AVERAGE(INDEX(D3:CC3,LARGE(IF(D3:CC3<"",COLUMN(D 3: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!

In article
,
streetboarder
wrote:

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

Averaging Last 6 Months
 

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
------------------------------------------------------------------------
streetboarder's Profile: http://www.excelforum.com/member.php...o&userid=30707
View this thread: http://www.excelforum.com/showthread...hreadid=511977

Averaging Last 6 Months
 

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
------------------------------------------------------------------------
streetboarder's Profile: http://www.excelforum.com/member.php...o&userid=30707
View this thread: http://www.excelforum.com/showthread...hreadid=511977

Averaging Last 6 Months
 

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?

In article
,
streetboarder
wrote:

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

Averaging Last 6 Months
 

Ok. After a little more information from the form user I have found that
I need to do the exact same thing but ingonore the last cell with
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 help
me Domenic? I have done the same in the past but have had to use macros
to change it each time - I wasn't sure it could be done in a formula
until now so thanks!

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

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

D6 refers the average formula we are working on. K6 refers the the most
current month. In the case about it would be July. Is there a way I can
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!!
Josh


--
streetboarder
------------------------------------------------------------------------
streetboarder's Profile: http://www.excelforum.com/member.php...o&userid=30707
View this thread: http://www.excelforum.com/showthread...hreadid=511977

Averaging Last 6 Months
 

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

=AVERAGE(INDEX(D3:CC3,LARGE(IF(D3:CC3<"",COLUMN(D 3: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!

In article
,
streetboarder
wrote:

Ok. After a little more information from the form user I have found that
I need to do the exact same thing but ingonore the last cell with
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 help
me Domenic? I have done the same in the past but have had to use macros
to change it each time - I wasn't sure it could be done in a formula
until now so thanks!

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

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

D6 refers the average formula we are working on. K6 refers the the most
current month. In the case about it would be July. Is there a way I can
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!!
Josh

Averaging Last 6 Months
 

Thank you so much!

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

Thanks again!
Josh


--
streetboarder
------------------------------------------------------------------------
streetboarder's Profile: http://www.excelforum.com/member.php...o&userid=30707
View this thread: http://www.excelforum.com/showthread...hreadid=511977

Averaging Last 6 Months
 

In article
wrote:

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!