Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need help to make a not-so-easy AVERAGE formula!
Here's a description of spreadsheet that I am making for the sales of a sto
- Column A has all the dates of the year (January 1, January 2, January 3, etc.) for the entire year listed one after another (so a total of 365 rows, starting in row 1). - Column B has the day of the week (Monday, Tuesday, Wednesday, etc.) corresponding to the date next to it in column A. - Column C has the sales amount (example: $125.50) corresponding to the date/day listed in the same row of columns A and B. I have the following formula (which works great) which gives me the SUM of all sales made ONLY on MONDAYS for the entire year to appear in a cell: =SUMIF(B:B,"MONDAY",C:C) My question is: Is there a formula that would allow Excel to calculate the AVERAGE (excluding blank cells and cells with zeros!) of all sales made ONLY on MONDAYS for the entire year? I have tried to modify the formula above to do that, but to no avail. I know not what I am doing! Can anyone help? Thanks in advance! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need help to make a not-so-easy AVERAGE formula!
Use AVERAGEIFS(C:C,A:A,"Monday",C:C,"0"). If you do not have Excel 2007, use
SUMIF()/COUNTIF() together. "sandres74" wrote: Here's a description of spreadsheet that I am making for the sales of a sto - Column A has all the dates of the year (January 1, January 2, January 3, etc.) for the entire year listed one after another (so a total of 365 rows, starting in row 1). - Column B has the day of the week (Monday, Tuesday, Wednesday, etc.) corresponding to the date next to it in column A. - Column C has the sales amount (example: $125.50) corresponding to the date/day listed in the same row of columns A and B. I have the following formula (which works great) which gives me the SUM of all sales made ONLY on MONDAYS for the entire year to appear in a cell: =SUMIF(B:B,"MONDAY",C:C) My question is: Is there a formula that would allow Excel to calculate the AVERAGE (excluding blank cells and cells with zeros!) of all sales made ONLY on MONDAYS for the entire year? I have tried to modify the formula above to do that, but to no avail. I know not what I am doing! Can anyone help? Thanks in advance! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need help to make a not-so-easy AVERAGE formula!
Since you already have the sum, you just need to divide by the count of the
non-zero, non-blank, values: =SUMIF(B:B,"MONDAY",C:C)/SUMPRODUCT(--(B1:B365="Monday"),--(C1:C365<0)) -- Gary''s Student - gsnu200909 "sandres74" wrote: Here's a description of spreadsheet that I am making for the sales of a sto - Column A has all the dates of the year (January 1, January 2, January 3, etc.) for the entire year listed one after another (so a total of 365 rows, starting in row 1). - Column B has the day of the week (Monday, Tuesday, Wednesday, etc.) corresponding to the date next to it in column A. - Column C has the sales amount (example: $125.50) corresponding to the date/day listed in the same row of columns A and B. I have the following formula (which works great) which gives me the SUM of all sales made ONLY on MONDAYS for the entire year to appear in a cell: =SUMIF(B:B,"MONDAY",C:C) My question is: Is there a formula that would allow Excel to calculate the AVERAGE (excluding blank cells and cells with zeros!) of all sales made ONLY on MONDAYS for the entire year? I have tried to modify the formula above to do that, but to no avail. I know not what I am doing! Can anyone help? Thanks in advance! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need help to make a not-so-easy AVERAGE formula!
Hi,
This depends on PROPERLY formatted dates and to prevent the formula becoming too long I used cell references for the criteria D1= 1/1/2009 D2 = 31/12/2009 D3 = Monday Note D3 is simply text, no formula now to get the day of the week in column B I used in B1 =Text(A1,"dddd") and dragged down Now the array formula. See below on how to enter an array formula =AVERAGE(IF(A1:A365=D1,IF(A1:A365<=D2,IF(B1:B365= D3,IF(C1:C3650,C1:C365))))) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "sandres74" wrote: Here's a description of spreadsheet that I am making for the sales of a sto - Column A has all the dates of the year (January 1, January 2, January 3, etc.) for the entire year listed one after another (so a total of 365 rows, starting in row 1). - Column B has the day of the week (Monday, Tuesday, Wednesday, etc.) corresponding to the date next to it in column A. - Column C has the sales amount (example: $125.50) corresponding to the date/day listed in the same row of columns A and B. I have the following formula (which works great) which gives me the SUM of all sales made ONLY on MONDAYS for the entire year to appear in a cell: =SUMIF(B:B,"MONDAY",C:C) My question is: Is there a formula that would allow Excel to calculate the AVERAGE (excluding blank cells and cells with zeros!) of all sales made ONLY on MONDAYS for the entire year? I have tried to modify the formula above to do that, but to no avail. I know not what I am doing! Can anyone help? Thanks in advance! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need help to make a not-so-easy AVERAGE formula!
While the first formula is good, the second formula for earlier versions
doesn't ignore zero values. Could do: =SUMIF(B:B,"MONDAY",C:C)/SUMPRODUCT(--(B$1:B$365="MONDAY"),--(C$1:C$3650)) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "rslaughter5" wrote: Use AVERAGEIFS(C:C,A:A,"Monday",C:C,"0"). If you do not have Excel 2007, use SUMIF()/COUNTIF() together. "sandres74" wrote: Here's a description of spreadsheet that I am making for the sales of a sto - Column A has all the dates of the year (January 1, January 2, January 3, etc.) for the entire year listed one after another (so a total of 365 rows, starting in row 1). - Column B has the day of the week (Monday, Tuesday, Wednesday, etc.) corresponding to the date next to it in column A. - Column C has the sales amount (example: $125.50) corresponding to the date/day listed in the same row of columns A and B. I have the following formula (which works great) which gives me the SUM of all sales made ONLY on MONDAYS for the entire year to appear in a cell: =SUMIF(B:B,"MONDAY",C:C) My question is: Is there a formula that would allow Excel to calculate the AVERAGE (excluding blank cells and cells with zeros!) of all sales made ONLY on MONDAYS for the entire year? I have tried to modify the formula above to do that, but to no avail. I know not what I am doing! Can anyone help? Thanks in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Make chart formatting as easy as it was in 2003! | Charts and Charting in Excel | |||
make it easy to turn off the little paste option graphics | Excel Worksheet Functions | |||
make bug reports easy | Excel Discussion (Misc queries) | |||
Easy way to make negative data positive? | Charts and Charting in Excel | |||
How do I make each row add up seperatly? Please make it easy... | New Users to Excel |