View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.misc
Domenic
 
Posts: n/a
Default 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