I am using the following formula to calculate the average sales for a
specific point in time and it works

However when I close the sheet and reopen it and then update the values it
returns and #Value error until I open up the source sheet.

Is there a way to avoid this?


--
ce


"Paul C" wrote:

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



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




--
ce

You can't use either SUMIF or COUNTIF on closed files.

If you're using Excel 2007 then use the AVERAGEIF function.

If you're not using Excel 2007 then you can use SUMPRODUCT.

--
Biff
Microsoft Excel MVP


"Curtis" .(do not spam) wrote in message
...
I am using the following formula to calculate the average sales for a
specific point in time and it works

However when I close the sheet and reopen it and then update the values it
returns and #Value error until I open up the source sheet.

Is there a way to avoid this?


--
ce


"Paul C" wrote:

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



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




--
ce

SUMPRODUCT seems to work so I changed the formula to

=(SUMPRODUCT(--('[0910-05-Sales Summary
Report.xlsx]BST'!$B$9:$B$39<=$D10)*('[0910-05-Sales Summary
Report.xlsx]BST'!$AE$9:$AE$39)))/$D10

Question is that one of the limitations of SUMIF??

Thanks
--
ce


"Curtis" wrote:

I am using the following formula to calculate the average sales for a
specific point in time and it works

However when I close the sheet and reopen it and then update the values it
returns and #Value error until I open up the source sheet.

Is there a way to avoid this?


--
ce


"Paul C" wrote:

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



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




--
ce

Question is that one of the limitations of SUMIF??

SUMIF won't work on a closed file.

--
Biff
Microsoft Excel MVP


"Curtis" .(do not spam) wrote in message
...
SUMPRODUCT seems to work so I changed the formula to

=(SUMPRODUCT(--('[0910-05-Sales Summary
Report.xlsx]BST'!$B$9:$B$39<=$D10)*('[0910-05-Sales Summary
Report.xlsx]BST'!$AE$9:$AE$39)))/$D10

Question is that one of the limitations of SUMIF??

Thanks
--
ce


"Curtis" wrote:

I am using the following formula to calculate the average sales for a
specific point in time and it works

However when I close the sheet and reopen it and then update the values
it
returns and #Value error until I open up the source sheet.

Is there a way to avoid this?


--
ce


"Paul C" wrote:

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



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




--
ce