Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Average starting with first month
I have a large worksheet of sales data where products are down the rows and
months are across columns. I would like a formula to calculate the average monthly sales but only inlude those months starting with the month of first sale going forward. If a sale month is zero the cell is blank Example worksheet: Mo1 / Mo2 / Mo3 / Mo4 / Mo 5 Product 1 10 / 10 / 8 / 8 / 9 Avg = 9 (Avg includes all months) Product 2 / 5 / 6 / 7 / 6 Avg = 6 (Avg excludes Mo 1 of zero) Product 3 10 / / 8 / 7 / Avg = 5 (Avg includes Mo 2 & 5 of zero) etc Any ideas? Thanks, Jim. |
#2
|
|||
|
|||
Average starting with first month
If the cell "...contains text, logical values, or empty cells, those values
are ignored; however, cells with the value zero are included." So the quick fix is to insert zeros for the blanks to be included (eg mo 2 and 5 for product 3 in your example -- product 2 works correctly anyway). You could do this easily by formula: create a second worksheet by copying the first. Then insert formulas to copy the data from the first worksheet: if the cell contains a number, use it; else if the cell to its left contains a number, use 0; else insert blank. Eg if the data starts on sheet 1 at cell B2, then on sheet 2 cell B2 use =IF(Sheet1!B20,Sheet1!B2,IF(Sheet1!A2<"",0,"")) "Jim" wrote in message ... I have a large worksheet of sales data where products are down the rows and months are across columns. I would like a formula to calculate the average monthly sales but only inlude those months starting with the month of first sale going forward. If a sale month is zero the cell is blank Example worksheet: Mo1 / Mo2 / Mo3 / Mo4 / Mo 5 Product 1 10 / 10 / 8 / 8 / 9 Avg = 9 (Avg includes all months) Product 2 / 5 / 6 / 7 / 6 Avg = 6 (Avg excludes Mo 1 of zero) Product 3 10 / / 8 / 7 / Avg = 5 (Avg includes Mo 2 & 5 of zero) etc Any ideas? Thanks, Jim. |
#3
|
|||
|
|||
Average starting with first month
Can't you just average each row, average does not include blanks as opposed
to zeros and from your example it looks like the cells are empty -- Regards, Peo Sjoblom (No private emails please) "Jim" wrote in message ... I have a large worksheet of sales data where products are down the rows and months are across columns. I would like a formula to calculate the average monthly sales but only inlude those months starting with the month of first sale going forward. If a sale month is zero the cell is blank Example worksheet: Mo1 / Mo2 / Mo3 / Mo4 / Mo 5 Product 1 10 / 10 / 8 / 8 / 9 Avg = 9 (Avg includes all months) Product 2 / 5 / 6 / 7 / 6 Avg = 6 (Avg excludes Mo 1 of zero) Product 3 10 / / 8 / 7 / Avg = 5 (Avg includes Mo 2 & 5 of zero) etc Any ideas? Thanks, Jim. |
#4
|
|||
|
|||
Average starting with first month
But Jim needs to have mo2 and mo5 treated as 0's in product 3.
If I were doing this, I'd put 0's where I need 0's and N/A in the cells that didn't apply. But that doesn't get YOU off the hook! Peo Sjoblom wrote: Can't you just average each row, average does not include blanks as opposed to zeros and from your example it looks like the cells are empty -- Regards, Peo Sjoblom (No private emails please) "Jim" wrote in message ... I have a large worksheet of sales data where products are down the rows and months are across columns. I would like a formula to calculate the average monthly sales but only inlude those months starting with the month of first sale going forward. If a sale month is zero the cell is blank Example worksheet: Mo1 / Mo2 / Mo3 / Mo4 / Mo 5 Product 1 10 / 10 / 8 / 8 / 9 Avg = 9 (Avg includes all months) Product 2 / 5 / 6 / 7 / 6 Avg = 6 (Avg excludes Mo 1 of zero) Product 3 10 / / 8 / 7 / Avg = 5 (Avg includes Mo 2 & 5 of zero) etc Any ideas? Thanks, Jim. -- Dave Peterson |
#5
|
|||
|
|||
Average starting with first month
This worked ok for me (until Peo comes back with a prettier response!):
=SUM(B2:F2)/(6-MATCH(TRUE,B2:F2<"",0)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Maybe better if all the months could be empty: =IF(COUNT(B2:F2)=0,"No data",SUM(B2:F2)/(6-MATCH(TRUE,B2:F2<"",0))) (Still an array formula.) Jim wrote: I have a large worksheet of sales data where products are down the rows and months are across columns. I would like a formula to calculate the average monthly sales but only inlude those months starting with the month of first sale going forward. If a sale month is zero the cell is blank Example worksheet: Mo1 / Mo2 / Mo3 / Mo4 / Mo 5 Product 1 10 / 10 / 8 / 8 / 9 Avg = 9 (Avg includes all months) Product 2 / 5 / 6 / 7 / 6 Avg = 6 (Avg excludes Mo 1 of zero) Product 3 10 / / 8 / 7 / Avg = 5 (Avg includes Mo 2 & 5 of zero) etc Any ideas? Thanks, Jim. -- Dave Peterson |
#6
|
|||
|
|||
Average starting with first month
Aha
-- Regards, Peo Sjoblom (No private emails please) "Dave Peterson" wrote in message ... But Jim needs to have mo2 and mo5 treated as 0's in product 3. If I were doing this, I'd put 0's where I need 0's and N/A in the cells that didn't apply. But that doesn't get YOU off the hook! Peo Sjoblom wrote: Can't you just average each row, average does not include blanks as opposed to zeros and from your example it looks like the cells are empty -- Regards, Peo Sjoblom (No private emails please) "Jim" wrote in message ... I have a large worksheet of sales data where products are down the rows and months are across columns. I would like a formula to calculate the average monthly sales but only inlude those months starting with the month of first sale going forward. If a sale month is zero the cell is blank Example worksheet: Mo1 / Mo2 / Mo3 / Mo4 / Mo 5 Product 1 10 / 10 / 8 / 8 / 9 Avg = 9 (Avg includes all months) Product 2 / 5 / 6 / 7 / 6 Avg = 6 (Avg excludes Mo 1 of zero) Product 3 10 / / 8 / 7 / Avg = 5 (Avg includes Mo 2 & 5 of zero) etc Any ideas? Thanks, Jim. -- Dave Peterson |
#7
|
|||
|
|||
Average starting with first month
That is pretty enough
-- Regards, Peo Sjoblom (No private emails please) "Dave Peterson" wrote in message ... This worked ok for me (until Peo comes back with a prettier response!): =SUM(B2:F2)/(6-MATCH(TRUE,B2:F2<"",0)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Maybe better if all the months could be empty: =IF(COUNT(B2:F2)=0,"No data",SUM(B2:F2)/(6-MATCH(TRUE,B2:F2<"",0))) (Still an array formula.) Jim wrote: I have a large worksheet of sales data where products are down the rows and months are across columns. I would like a formula to calculate the average monthly sales but only inlude those months starting with the month of first sale going forward. If a sale month is zero the cell is blank Example worksheet: Mo1 / Mo2 / Mo3 / Mo4 / Mo 5 Product 1 10 / 10 / 8 / 8 / 9 Avg = 9 (Avg includes all months) Product 2 / 5 / 6 / 7 / 6 Avg = 6 (Avg excludes Mo 1 of zero) Product 3 10 / / 8 / 7 / Avg = 5 (Avg includes Mo 2 & 5 of zero) etc Any ideas? Thanks, Jim. -- Dave Peterson |
#8
|
|||
|
|||
Average starting with first month
Awesome! Thanks Dave, this is exactly the solution I needed. Thank you for
taking the time to look into this and to share your expertise. Regards, Jim "Dave Peterson" wrote: This worked ok for me (until Peo comes back with a prettier response!): =SUM(B2:F2)/(6-MATCH(TRUE,B2:F2<"",0)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Maybe better if all the months could be empty: =IF(COUNT(B2:F2)=0,"No data",SUM(B2:F2)/(6-MATCH(TRUE,B2:F2<"",0))) (Still an array formula.) Jim wrote: I have a large worksheet of sales data where products are down the rows and months are across columns. I would like a formula to calculate the average monthly sales but only inlude those months starting with the month of first sale going forward. If a sale month is zero the cell is blank Example worksheet: Mo1 / Mo2 / Mo3 / Mo4 / Mo 5 Product 1 10 / 10 / 8 / 8 / 9 Avg = 9 (Avg includes all months) Product 2 / 5 / 6 / 7 / 6 Avg = 6 (Avg excludes Mo 1 of zero) Product 3 10 / / 8 / 7 / Avg = 5 (Avg includes Mo 2 & 5 of zero) etc Any ideas? Thanks, Jim. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i chart a 24 month moving average of sales shipments | Charts and Charting in Excel | |||
Calculating Month Average Exch Rate from Exchange Rates Calendar!!! | Excel Worksheet Functions | |||
How do I get the average price per bid for an individual month? | Excel Discussion (Misc queries) | |||
What is this kind of average called? | Excel Worksheet Functions | |||
average, array and offsets | Excel Worksheet Functions |