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