Average with Condition

[Curtis]

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

 

[Paul C]

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)

 

[Curtis]

Thanks

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

thanks again

 

[Curtis]

#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?

 

[Paul C]

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:AE39<>0))

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.