Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to keep previous months' data | Excel Worksheet Functions | |||
Add # of months and get result last day of # months. | Excel Discussion (Misc queries) | |||
Changing the range of several averaging functions | Excel Discussion (Misc queries) | |||
months between 2 dates!!! | Excel Discussion (Misc queries) | |||
Why "datedif" function results sometimes negative numbers? | Excel Worksheet Functions |