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
|