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
[email protected]
"streetboarder" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[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!

In article
<[email protected]>,
streetboarder
<[email protected]> 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

[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?

In article
<[email protected]>,
streetboarder
<[email protected]> 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

[streetboarder]

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(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 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

[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!

In article
<[email protected]>,
streetboarder
<[email protected]> 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(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 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]

Thank you so much!

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

Thanks again!
Josh

[Domenic]

In article <[email protected]>
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!