Is there a formula for averaging the remaining?

Angie Hatfield

Hello again!

I have been trying to figure out a formula for averaging the remaining.

This sheet has all business days through Dec 8 and a certain number of tasks to complete each day to be finished on time (Dec. 8).

There are two columns. One has the number of tasks due to be completed each day (labeled Average) and the other is blank (labeled Done).

Daily the number of tasks completed that day is entered into the Done column.

I need to figure out the average daily amount of tasks remaining. We've determined that if the number reaches a threshhold, someone needs to jump in and help or if we are going to have less to do each day.

I hope that makes sense. 

Thank you much for your time and assistance.

Angie Hatfield

Mike Wilday

Do you have an example of the fields that you talking about? Seeing it might help the community best determine how to create your formula. 

Angie Hatfield

Thank you Mike. If this example is not clear, please let me know.

Column names have been changed from yesterday.

At the bottom of column labeled "Total"  you will see 190 repeated. This number changes when column "Moved" is updated and provides the remaining amount to be moved. I need to know the average to be done daily based on that number. The end date is Dec. 8. 

I appreciate you help. Thank you

Mike Wilday

I think I understand. you want to take the final number (190) from the Dec 8 column and divide it by the number of days left (without moved tasks)... so you can see how many tasks per day you have to do? is that correct? If so, try this formula. It will count all of the blank cells in the range of moved to count how many days are left and divide that into the total task number to give you an average of how many tasks need to be completed each day to get them done. 

=Total* / Countif(moved**:moved***, "")

* - replace with row number of final task

** - replace with row number of first task

*** replace with row number of final task

Angie Hatfield

That gives me the total remaining, but not the average that needs to be done daily to complete moving them all by Dec. 8.

So using tomorrow through Dec 8, there are 41 days to move the remaining 199. Using that, the average per day would be 4.85 per day. 

How do write it to automatically update every day when/if the "Total" number changes and there are less days to complete it?

 

Thanks again!

Mike Wilday

Does the total number of tasks float down to the final row? The formula should give you that number. 

I'll break it down for you. 

=Total* / Countif(moved**:moved***, "")

=Total* (This is the total number of tasks remaining- I assumed your total number of tasks is tallying down to the final day in your Total column -- if not, replace this with whatever your total tasks are. 

/ - divided by

Countif(moved**:moved***,"") This part of the formula is counting the number of blank days left. 

Essentially you are taking the total and dividing it by total days left. 

This should work. Can you share the formula as you created with your row numbers? 

Angie Hatfield

The formula used for remaining count is: =Total15 + [Dealers Moved]16

I tried the formula you gave me again =Total16 / COUNTIF([Dealers Moved]16:[Dealers Moved]16, "") and when I add numbers to the Dealers Moved column that it was giving me an increased amount.

If I don't add any numbers, the numbers just matches the total column.

Thanks again!

 

Mike Wilday

Yes, you need to put the range instead of Dealers Moved 16: Dealers moved 16... it has to count all your blank rows in the range. So it needs to be the first and last row in the range so it will count the days that haven't had dealers moved. 

It would be [Dealers Moved]1:[Dealers Moved]3000 (or whatever row number your december deadline is. 

Mike Wilday

You were getting the same number because it was counting only one row. And there were no blanks so it was dividing by 0. 

Angie Hatfield

Mike, I am still getting an increasing number when I add the amount to Dealers Moved column. The Total will decrease by that amount, so the average should decrease as well.

I wish I understood formulas better, I appreciate your help.

Mike Wilday

As I look at your sheet... I am noticing that the Dealers Moved column is increasing your total rather than decreasing it. I think I may not have the right columns for you to calculate the total like you are wanting. What is the name of the column of tasks Done? As Dealers are Moved should the total increase or decrease? I wonder if that is at the root of the problem. You mentioned that as tasks are completed, the number total would decrease. 

J. Craig Williams

Jumping in here.

It appears the [Total] column is counting up the daily [Dealers Moved] tally. The first image shows a date column.

Are the [Dealers Moved] NEW tasks that have to be completed or OLD tasks that HAVE been completed?

If NEW -- where is the count for what has to be completed?

If OLD -- are there new tasks or only a fixed count of tasks that need to be completed before Dec 8th?

[Remaining Tasks] = [Total Tasks] - [Tasks Completed]

Is [Total Tasks] fixed or it is

[Total Tasks] = [Total From Yesterday] + [New Tasks Added Today]

[AVERAGE REMAINING TASKS] = [Total Tasks] / [Work Days Until Dec 8]

...

If [Total] is counting up [Tasks Completed] then unless you really need the [Total Column] (perhaps for a run-chart), then [Total] would be 

= SUM([Daily Moved]:[Daily Moved]) 

and can be located in a single cell.

In another cell, the [Remaining Tasks] would be

= SUM([Daily Moved]:[Daily Moved]) / NETWORKDAYS(TODAY(), DATE(2017, 12, 8))

or 

= (cell reference to total) / NETWORKDAYS(TODAY(), DATE(2017, 12, 8))

On Dec 8th, this will throw an error, so you'll need to decide what happen on and after Dec 8th.

I hope that helps.

Craig

 

 

Angie Hatfield

Good morning,

Dealers Moved are those completed daily (done daily). Total is the running total moved. There are 1078 moved, 228 moved total and 850 remaining.

The date column is all of the work days that this work will be done.

 

thank you again.

 

J. Craig Williams

Then Remaining is 

=1078 - SUM([Daily Moved]:[Daily Moved]) 

whereever you are getting the 1078 from can replace the hard-coded number.

and average per work day will be 

= remaining-cell-reference / NETWORKDAYS(TODAY(), DATE(2017, 12, 8))

where remaining-cell-reference  is whereever you put the first formula.

I hope that helps.

Craig

Angie Hatfield

Craig,

This absolutely works. Thank you so much. I greatly appreciate it.

I hope you have a fantastic week.

Angie

J. Craig Williams

You are welcome. 

Craig