Averaging Formula

[cmiedaner]

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

 

[Ben McClave]

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.

 

[joeu2004]

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)

 

[cmiedaner]

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.

 

[Ben McClave]

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.

 

[joeu2004]

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.

 

[cmiedaner]

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 ?

 

[joeu2004]

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.

 

[cmiedaner]

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.