Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average if is not greater than today
This is a two part question. I have a table with sales data that I am trying to average and chart. Each row repesents data for each month (36 rows one for each month for 3 years worth of data). The information is all being pulled from other worksheets and in months that have not occurred yet (Sept, oct, nov, dec) the data is a "0". 1. I need a formula that will average only the current month and months that have already occurred keeping in mind that the average is more than a 1yr history. 2. I need to figure out how to apply data in row F to a chart so that the chart is only charting data from the current month back and not including future months. A2:37 contains date in mmm-yy format F2:37 contains data to be averaged (and data for chart) -- jermsalerms ------------------------------------------------------------------------ jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167 View this thread: http://www.excelforum.com/showthread...hreadid=574295 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average if is not greater than today
= Average(If(A2:A37<Today(),F2:F27,""))
Press Ctrl+Shift+Enter as this is an array formula as for the second question, Goto Tools, Options, Chart Tab, Check Active Chart Plot empty values as: Not Plotted Charles Chickering jermsalerms wrote: This is a two part question. I have a table with sales data that I am trying to average and chart. Each row repesents data for each month (36 rows one for each month for 3 years worth of data). The information is all being pulled from other worksheets and in months that have not occurred yet (Sept, oct, nov, dec) the data is a "0". 1. I need a formula that will average only the current month and months that have already occurred keeping in mind that the average is more than a 1yr history. 2. I need to figure out how to apply data in row F to a chart so that the chart is only charting data from the current month back and not including future months. A2:37 contains date in mmm-yy format F2:37 contains data to be averaged (and data for chart) -- jermsalerms ------------------------------------------------------------------------ jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167 View this thread: http://www.excelforum.com/showthread...hreadid=574295 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average if is not greater than today
the first part worked great...except I was wondering if you know a variation of this that WOULD NOT count a cell that has a "0" in it if the date IS less than today. There are some months were there are no tansactions and I would like to exclude this from the average for a particular column. Also the question about the chart did not work since the cells that respresent future transaction have zeros in them. -- jermsalerms ------------------------------------------------------------------------ jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167 View this thread: http://www.excelforum.com/showthread...hreadid=574295 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average if is not greater than today
Nest another if statement to fix the zero's
= Average(If(A2:A37<Today(),If(F2:F27<0,F2:F27,""), "")) HTH Charles Chickering jermsalerms wrote: the first part worked great...except I was wondering if you know a variation of this that WOULD NOT count a cell that has a "0" in it if the date IS less than today. There are some months were there are no tansactions and I would like to exclude this from the average for a particular column. Also the question about the chart did not work since the cells that respresent future transaction have zeros in them. -- jermsalerms ------------------------------------------------------------------------ jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167 View this thread: http://www.excelforum.com/showthread...hreadid=574295 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average if is not greater than today
that extra if statement worked great. Any ideas on part two (the charting)....based on my last post. " Also the question about the chart did not work since the cells that respresent future transaction have zeros in them. For example: A2 = 6/1/06 F2 = 4.0% A3 = 7/1/06 F3 = 3.5% A4 = 8/1/06 F4 = 2.9% A5 = 9/1/06 F5 = 2.1% A6 = 10/1/06 F6 = 0.0% For my chart I only want to chart out information that is < Today(). So that if I look at the chart today it will chart F2:F4, but if I look at the chart next month it will chart F2:F5. " -- jermsalerms ------------------------------------------------------------------------ jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167 View this thread: http://www.excelforum.com/showthread...hreadid=574295 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to average a column of numbers that are greater than 0? | Excel Worksheet Functions | |||
Average using Greater Than - Less Than | Excel Discussion (Misc queries) | |||
Average greater than 0 | Excel Discussion (Misc queries) | |||
Countif cell greater than average | Excel Worksheet Functions | |||
Addition to Turn cell red if today is greater or equal to date in cell | New Users to Excel |