Prev: Moving cell formula
Next: Auto calculate
From: Curtis on 17 Mar 2010 15:35
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
From: Paul C on 17 Mar 2010 16:12
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
From: Curtis on 17 Mar 2010 16:51
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
From: Curtis on 18 Mar 2010 10:45
#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
From: Paul C on 18 Mar 2010 14:48
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.


--
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
 | 
Pages: 1
Prev: Moving cell formula
Next: Auto calculate