A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Average with Condition



 
 
Thread Tools Display Modes
  #1  
Old March 17th, 2010, 08:35 PM posted to microsoft.public.excel.worksheet.functions
Curtis[_6_]
external usenet poster
 
Posts: 18
Default Average with Condition

I have a report sheet where

Cell b10 represent the day of the month

I need a formula that will look to a prior month sheet for the corresponding
day as identified in B10 and average the sum sales to that point

In the source sheet

Column B (rows 9 thru 39) = day of month
Column AE (row 9 thru 39) = sales
--
ce
  #2  
Old March 17th, 2010, 09:12 PM posted to microsoft.public.excel.worksheet.functions
Paul C
external usenet poster
 
Posts: 202
Default Average with Condition

In Excel 2007 there is a AVERAGEIF function (look it up in help) that should
do the trick

If you are in Excel 2003 this is not available but you can still achieve
your result by doing a conditional sum and dividing by the number of days
like this

=SUMIF(Sheet1!B9:B39,"=" & B10,Sheet1!AE9:AE39)/COUNTIF(Sheet1!B9:B39,"="
& B10)
--
If this helps, please remember to click yes.


"Curtis" wrote:

I have a report sheet where

Cell b10 represent the day of the month

I need a formula that will look to a prior month sheet for the corresponding
day as identified in B10 and average the sum sales to that point

In the source sheet

Column B (rows 9 thru 39) = day of month
Column AE (row 9 thru 39) = sales
--
ce

  #3  
Old March 17th, 2010, 09:51 PM posted to microsoft.public.excel.worksheet.functions
Curtis[_6_]
external usenet poster
 
Posts: 18
Default Average with Condition

Thanks

Can you help with a condition that does not include days where there are no
sales

thanks again
--
ce


"Paul C" wrote:

In Excel 2007 there is a AVERAGEIF function (look it up in help) that should
do the trick

If you are in Excel 2003 this is not available but you can still achieve
your result by doing a conditional sum and dividing by the number of days
like this

=SUMIF(Sheet1!B9:B39,"=" & B10,Sheet1!AE9:AE39)/COUNTIF(Sheet1!B9:B39,"="
& B10)
--
If this helps, please remember to click yes.


"Curtis" wrote:

I have a report sheet where

Cell b10 represent the day of the month

I need a formula that will look to a prior month sheet for the corresponding
day as identified in B10 and average the sum sales to that point

In the source sheet

Column B (rows 9 thru 39) = day of month
Column AE (row 9 thru 39) = sales
--
ce

  #4  
Old March 18th, 2010, 03:45 PM posted to microsoft.public.excel.worksheet.functions
Curtis[_6_]
external usenet poster
 
Posts: 18
Default Average with Condition

#Value error when I use this function

When I open up this file and update the contents it returns an value error
unless I also open up the source document. Is there a way around this?
--
ce


"Paul C" wrote:

In Excel 2007 there is a AVERAGEIF function (look it up in help) that should
do the trick

If you are in Excel 2003 this is not available but you can still achieve
your result by doing a conditional sum and dividing by the number of days
like this

=SUMIF(Sheet1!B9:B39,"=" & B10,Sheet1!AE9:AE39)/COUNTIF(Sheet1!B9:B39,"="
& B10)
--
If this helps, please remember to click yes.


"Curtis" wrote:

I have a report sheet where

Cell b10 represent the day of the month

I need a formula that will look to a prior month sheet for the corresponding
day as identified in B10 and average the sum sales to that point

In the source sheet

Column B (rows 9 thru 39) = day of month
Column AE (row 9 thru 39) = sales
--
ce

  #5  
Old March 18th, 2010, 07:48 PM posted to microsoft.public.excel.worksheet.functions
Paul C
external usenet poster
 
Posts: 202
Default Average with Condition

In Excel 2007 the AVERAGEIFS function will easily allow you to specifiy
multiple conditions

In 2003 you need to change you count to exclude the 0. SumProduct works for
this

=SUMIF(Sheet1!B9:B39,"=" &
B10,Sheet1!AE9:AE39)/SUMPRODUCT(--(Sheet1!B9:B39=B10),--(Sheet1!AE9:AE390))

The sumproduct conditions evaluate as arrays and True=1 and False=0. The
two conditions multipled together evalute anything after the date in B10 and
any daily sales equal to 0 as 0 to give the correct count

This site has a much more detailed explanation
http://xldynamic.com/source/xld.SUMPRODUCT.html

They also cover the closed workbook issue, which SUMPRODUCT is supposed to
handle better. You could convert the SUMIF to a SUMPRODUCT and it may take
care of that problem.

I am not really sure about this, beacuse I don't make extensive use of
linked formulas. If you still have a issue with this you should post a new
question (to improve visibility) and perhaps someone else has a better answer.

Hope this helps point you in the right direction.


--
If this helps, please remember to click yes.


"Curtis" wrote:

#Value error when I use this function

When I open up this file and update the contents it returns an value error
unless I also open up the source document. Is there a way around this?
--
ce


"Paul C" wrote:

In Excel 2007 there is a AVERAGEIF function (look it up in help) that should
do the trick

If you are in Excel 2003 this is not available but you can still achieve
your result by doing a conditional sum and dividing by the number of days
like this

=SUMIF(Sheet1!B9:B39,"=" & B10,Sheet1!AE9:AE39)/COUNTIF(Sheet1!B9:B39,"="
& B10)
--
If this helps, please remember to click yes.


"Curtis" wrote:

I have a report sheet where

Cell b10 represent the day of the month

I need a formula that will look to a prior month sheet for the corresponding
day as identified in B10 and average the sum sales to that point

In the source sheet

Column B (rows 9 thru 39) = day of month
Column AE (row 9 thru 39) = sales
--
ce

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:49 PM.


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