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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Average with Conditions
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 |
#2
|
|||
|
|||
Average with Conditions
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 |
#3
|
|||
|
|||
Average with Conditions
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 |
#4
|
|||
|
|||
Average with Conditions
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 |
Thread Tools | |
Display Modes | |
|
|