Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Averaging Formula
I have 20 days in my sales cycle. As each day passes, I record a sales figure in the associated cell. My sales goal is to hit 100 sales for the 20 day period. Given that, is there a formula that I can use to calculate the average sales per days that remain that I would need to make inorder to hit the goal of 100 sales ?
Day Sales 1 3 2 2 3 5 4 7 5 0 Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Averaging Formula
Hello,
Assuming that your data starts in cell A1, enter this formula in cell C2: =IF(SUM(B$2:B2)=100, 0, IF(A2=20, 100-SUM(B$2:B2) & " sales short!", (100-SUM(B$2:B2))/(20-A2))) Then copy the forumula down through day 20. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Averaging Formula
wrote:
I have 20 days in my sales cycle. As each day passes, I record a sales figure in the associated cell. My sales goal is to hit 100 sales for the 20 day period. Given that, is there a formula that I can use to calculate the average sales per days that remain that I would need to make inorder to hit the goal of 100 sales ? If your sales data are in B2:B21, then: =IF(COUNT(B2:B21)=20,0, ROUNDUP(MAX(0,100-SUM(B2:B21))/(20-COUNT(B2:B21)),0)) Note: That assumes that the sales data for the remaining days are left empty, not zero. If you would like to see a running average after each day, put the following formula into C2 and copy down through C21: =IF(COUNT($B$2:B2)=20,0, ROUNDUP(MAX(0,100-SUM($B$2:B2))/(20-COUNT($B$2:B2)),0)) The use of ROUNDUP ensures that you do not fall short. But with that average, you might reach your goal sooner. The use of MAX ensures that if you already reached your goal, the average is zero for the remaining days. The test for COUNT()=20 avoids a #DIV/0 error. Alternatively, if you have Excel 2007 or later and you do not require Excel 2003 compatibility, you could write: =IFERROR(ROUNDUP(MAX(0,100-SUM(B2:B21))/(20-COUNT(B2:B21)),0),0) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Averaging Formula
On Wednesday, December 5, 2012 1:41:17 PM UTC-5, wrote:
I have 20 days in my sales cycle. As each day passes, I record a sales figure in the associated cell. My sales goal is to hit 100 sales for the 20 day period. Given that, is there a formula that I can use to calculate the average sales per days that remain that I would need to make inorder to hit the goal of 100 sales ? Day Sales 1 3 2 2 3 5 4 7 5 0 Thanks in advance. Thanks all. Those work. It got me thinking of another sceanrio where this would be helpful. If I have 10 tests in my semester and my grades are like this: Test Score 1 50% 2 75% 3 80% 4 40% 5 6 7 8 9 10 What do I need to average on the remaining tests inorder to attain an 85% average. I tried to adapt the formulas posted but could not get it to work correctly. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Averaging Formula
Try:
=(8.5-SUM(B$2:B2))/MAX(10-A2, 1) or =IF(A2=10, IF(AVERAGE($B$2:B2)<0.85, TEXT(0.85-AVERAGE($B$2:B2), "0.00%") & " short", "success!"), (8.5-SUM(B$2:B2))/MAX(10-A2, 1)) Then copy the formula down the column. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Averaging Formula
wrote:
It got me thinking of another sceanrio where this would be helpful. If I have 10 tests in my semester and my grades are like this: [....] What do I need to average on the remaining tests inorder to attain an 85% average. I tried to adapt the formulas posted but could not get it to work correctly. In general, if D1 is the number of events (20 for sales days; 10 for scores), D2 is the goal (100 for sales; 85% for scores, which can also be written 0.85), and D3 is the round-up precision that you want (0 for sales; 2 for percentage -- explained below), the overall formula can be written: =IF(COUNT(B2:INDEX(B2:B1000,D1))=D1,0, ROUNDUP(MAX(0,D2-SUM(B2:INDEX(B2:B1000,D1)))/(D1-COUNT(B2:INDEX(B2:B1000,D1))),D3)) The expression INDEX(B2:B1000,D1) returns reference to B2 plus D1-1; so B2:INDEX(...) is the range from B2 to B2 plus D1-1. We might find the alternative expressions OFFSET(B2,0,0,D1) and INDIRECT("B2:B" & 2+D1-1) more readable. But those are "volatile" expressions; they cause that cell and any dependent cells to be recalculated whenever any cell in any worksheet in the workbook is modified (and some other times). That might not be so bad if you have a simple workbook. Otherwise, it can be the cause of significant delays. The round-up precision for percentage is 2, not 0, because 85% is actually the number 0.85, for example. If you find the ROUNDUP usage confusing, you can eliminate it. Simply write: =IF(COUNT(B2:INDEX(B2:B1000,D1))=D1,0, MAX(0,D2-SUM(B2:INDEX(B2:B1000,D1)))/(D1-COUNT(B2:INDEX(B2:B1000,D1)))) But beware that you might come up short of your goal due to rounding. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Averaging Formula
On Wednesday, December 5, 2012 1:41:17 PM UTC-5, wrote:
I have 20 days in my sales cycle. As each day passes, I record a sales figure in the associated cell. My sales goal is to hit 100 sales for the 20 day period. Given that, is there a formula that I can use to calculate the average sales per days that remain that I would need to make inorder to hit the goal of 100 sales ? Day Sales 1 3 2 2 3 5 4 7 5 0 Thanks in advance. Thanks. I am trying the formula from joeu2004 =IF(COUNT(B2:INDEX(B2:B1000;D1))=D1;0; ROUNDUP(MAX(0;D2-SUM(B1:INDEX(B2:B1000;D1)))/(D1-COUNT(B2:INDEX(B2:B1000;D1)));D3)) Values in B2 to B4 are .85, .60, .25. Values in D1 to D3 are 10, .8, 2. The formula is in F1. The result of the formula is 0 (zero) - which does not seem correct. What am I doing incorrectly ? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Averaging Formula
wrote:
I am trying the formula from joeu2004 =IF(COUNT(B2:INDEX(B2:B1000;D1))=D1;0; ROUNDUP(MAX(0;D2-SUM(B1:INDEX(B2:B1000;D1)))/ (D1-COUNT(B2:INDEX(B2:B1000;D1)));D3)) Values in B2 to B4 are .85, .60, .25. Values in D1 to D3 are 10, .8, 2. The formula is in F1. The result of the formula is 0 (zero) - which does not seem correct. What am I doing incorrectly ? There are two mistakes. The critical mistake is mine: D2 should be =.8*D1 in this case [1]. In general, D2 should be the goal __sum__. Since you want the __average__ of D1 cells to be 80%, their __sum__ should be 80%*D1. The second mistake is yours: SUM(B1:...) should be SUM(B2:...). It is a minor mistake if B1 does not contain a number. And do not forget to format F1 appropriately, for example Number with 2 or more decimal places or Percentage with 0 or more decimal places. ----- [1] It is prudent to write =ROUND(.8*D1,2) in D2. This avoids infinitesimal anomalies that creep into Excel arithmetic because Excel relies on the computer's native 64-bit binary floating-point to represent numbers and to perform arithmetic. Consequently, most non-integers like 0.8 cannot be represented exactly. For that reason, for example, IF(10.1-10=0.1,TRUE) returns FALSE(!), but IF(ROUND(10.1-10,2)=0.1,TRUE) returns TRUE. In this particular case, 0.8*10 does return exactly 8. But it is prudent not to rely on such coincidences. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Averaging Formula
On Wednesday, December 5, 2012 1:41:17 PM UTC-5, wrote:
I have 20 days in my sales cycle. As each day passes, I record a sales figure in the associated cell. My sales goal is to hit 100 sales for the 20 day period. Given that, is there a formula that I can use to calculate the average sales per days that remain that I would need to make inorder to hit the goal of 100 sales ? Day Sales 1 3 2 2 3 5 4 7 5 0 Thanks in advance. Thanks. That works well. And thank you for the in depth explanantion. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Averaging formula error | Excel Discussion (Misc queries) | |||
Averaging with a formula | Excel Discussion (Misc queries) | |||
Averaging Array Formula | Excel Discussion (Misc queries) | |||
Formula for averaging times | Excel Discussion (Misc queries) | |||
Averaging formula help, please. | Excel Programming |