Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 644
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 644
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to average a column of numbers that are greater than 0? JimNColorado Excel Worksheet Functions 13 December 11th 07 02:22 AM
Average using Greater Than - Less Than webnntp Excel Discussion (Misc queries) 3 August 1st 06 03:37 AM
Average greater than 0 Joker Excel Discussion (Misc queries) 3 March 6th 06 02:20 PM
Countif cell greater than average Mary Ann Excel Worksheet Functions 4 August 10th 05 09:49 AM
Addition to Turn cell red if today is greater or equal to date in cell Rich New Users to Excel 2 December 9th 04 03:06 AM


All times are GMT +1. The time now is 10:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"